Print elements from Array to a single worksheet-CodePudding

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

CodePudding user response：

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