Print elements from Array to a single worksheet

Question

I'm currently trying to output some results into a excel worksheet.

This is what I have so far:

Sub toExcel()

Dim arr() As Variant
Dim rows1 As Integer
Dim text As Variant
Dim regEx As New RegExp
Dim rows2 As Integer

regEx.Global = True
regEx.IgnoreCase = True


With ThisWorkbook.Worksheets("table1").Activate

   rowsShow = ActiveSheet.Cells(ActiveSheet.rows.count, "A").End(xlUp).row
   arr = Range("A1:A" & rows1).Value

    
End With

With regEx

 .Pattern = "\w+(_Test)"
 
End With

With ThisWorkbook.Worksheets("table2").Activate

For Each item1 In arr
    
    Set mc = regEx.Execute(item1)
    Dim item2 As Variant
    rows2 = 1
    
    Worksheets.Add

    Debug.Print mc.count

        For Each item2 In mc
        
            Debug.Print item
            ActiveSheet.Range("B" & rows2).Value = item
            rows2 = rows2 + 1
         
        Next item
             
Next text

But this displays only the last element of the array (Maybe because the others are overwritten?) How can i get it to displays all elements under each other? Also I want to have the matching "item1" to all of them in the A-row.

The arrays are dynamic so I don't have a explicit length.

In excel should it look like:

Excelsheet

First and foremost, [`avoid select/activate` wherever possible](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba#:~:text=Avoiding%20Select%20and%20Activate%20is,always%20considered%20the%20active%20one.). — Cyril, Aug 12 '22 at 13:09
A bit more in-depth, but what are you trying to do with `\w+(_Test)`? Can you explain more about your input and desired output? Maybe in a more visual representation? — JvdV, Aug 12 '22 at 13:39

score 1 · Accepted Answer · answered Aug 12 '22 at 13:16

Move rows2 = 1 outside of the loop and that should resolve your issue... also look at using Option Explicit (Tools, Options, check box for Require variable declaration). Not sure what happened with your For statements, but the swap of variable names doesn't seem correct (item2 & item, item1 & text).

rows2 = 1 'if this is inside the item1 loop, it resets back to 1 every time
For Each item1 In arr
    Set mc = regEx.Execute(item1)
    Dim item2 As Variant
    With ThisWorkbook
        Dim ws As Worksheet 'replaced your worksheet.add
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = item1 'may want error handling for same name
    End with
    Debug.Print mc.count
    For Each item2 In mc
        Debug.Print item2
        ws.Range("B" & rows2).Value = item2
        rows2 = rows2 + 1
    Next item2
Next item1

Oh yes, now there is the same amount of rows and matches. But now after the 5th row it's just printing the same result (that should not be like that, i've checked it) and from 1-4 it's in a random order :/ — lemurdroid, Aug 12 '22 at 13:29
@lemurdroid random order? do you have a mock-up of data you can add to your post? This would be looping through what you have outlined, in the order outlined. — Cyril, Aug 12 '22 at 13:40

Print elements from Array to a single worksheet

1 Answers1