VBA Double FOR loop with an IF statement

Question

I need a VBA script that will perform a double FOR loop. The first FOR loop, is a loop that needs to perform a few commands over multiple sheets (The first sheet is the main sheet and needs to be skipped!!)

The second for loop needs to compare values on multiple rows. I have pasted my code so far...


Public Sub LoopOverSheets()
device = Cells(6, 1) 'This value is whatever the user chooses from a drop-down menu
Dim mySheet As Worksheet 'Creating variable for worksheet
orow = 8 'setting the starting output row

For Each mySheet In ThisWorkbook.Sheets 'this is the first FOR loop, to loop through ALL the worksheets
tabName = ActiveSheet.Name    'this is a variable that holds the name of the active sheet

    For irow = 2 To 10 'This line of code starts the SECOND FOR loop.
        If (Range("a" & irow)) = device Then 'This line of code compares values
            orow = orow + 1
            Range("'SUMMARY'!a" & orow) = device 'This line of code pastes the value of device variable
            Range("'SUMMARY'!b" & orow) = tabName 'This line of code needs to paste the name of the current active sheet
            'Range("'SUMMARY'!c" & orow) = Range("'tabName'!b" & irow) 'this line of code needs to paste whatever value is in the other sheet's cell
            'Range("'SUMMARY'!d" & orow) = Range("'tabName'!c" & irow) 'same objective as the last line of code, different rows and columns
        End If
    Next irow 'This line of code will iterate to the next orow. This is where I get an error (Compile Error : Next Without For)*******
Next mySheet 'This line of code will iterate to the next sheet

End Sub

Currently the code runs, but it is only outputting results from the first (main sheet). It needs to skip the first sheet and iterate through the rest of them.

As commented on your previous question, normal practice is not to use the worksheet name inside `Range`, but instead to qualify the `Worksheet`: `Worksheets("SUMMARY").Range("A" & orow)`. — BigBen, Oct 22 '20 at 18:25
Either loop by index (`For i = 2 to ThisWorkbook.Sheets.Count`), or use an `If` to skip the first worksheet. — BigBen, Oct 22 '20 at 18:26
It's unclear from your code, but apparently you expect `Range("a" & irow)` to come from `mySheet`, and the other `Range`s from the sheet called Summary. Because you never [expressed that intention](https://stackoverflow.com/a/17790711/11683) with explicit sheet references, it does not happen. — GSerg, Oct 22 '20 at 18:28
Don't use the worksheet name (`tabName`) inside the loop either. Use `mySheet`: `mySheet.Range("B" & irow)`. — BigBen, Oct 22 '20 at 18:34

score 0 · Answer 1 · answered Oct 22 '20 at 19:10

Update Summary Worksheet

Link: StrComp Function

The following is not tested.

The Code

Option Explicit

Sub loopOverSheets()
    
    Const tName As String = "Summary"
    Const tFirstRow  As Long = 8
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The workbook containing this code.
    
    Dim tgt As Worksheet
    Set tgt = wb.Worksheets(tName)
    Dim device As String
    device = tgt.Cells(6, 1).Value
    Dim tRow As Long
    tRow = tFirstRow
    
    Dim src As Worksheet
    Dim sName As String
    Dim i As Long
    For Each src In wb.Worksheets
        sName = src.Name
        If Not StrComp(sName, tName, vbTextCompare) = 0 Then
            For i = 2 To 10
                If StrComp(src.Range("A" & i), device, vbTextCompare) = 0 Then
                    tRow = tRow + 1
                    tgt.Range("A" & tRow).Value = device
                    tgt.Range("B" & tRow).Value = sName
                    tgt.Range("C" & tRow).Value = src.Range("B" & i).Value
                    tgt.Range("D" & tRow).Value = src.Range("C" & i).Value
                End If
            Next i
        End If
    Next src

    MsgBox "Data transferred.", vbInformation, "Success"
    
End Sub

score -1 · Accepted Answer · edited Oct 23 '20 at 00:07

Dim irow As Long
Dim ws  As Worksheet
Dim mySheet As String
mySheet = "mainSheet" 'Insert in the mySheet variable the name of your main sheet
device = Cells(6, 1)
orow = 8
For Each ws In Application.ActiveWorkbook.Worksheets
    If ws.Name <> mySheet Then
        For irow = 2 To 10
            If ws.Range("A" & irow) = device Then
                orow = orow + 1
                Range("'SUMMARY'!a" & orow) = device
                Range("'SUMMARY'!b" & orow) = ws.Name
                'Range("'SUMMARY'!c" & orow) = Range("'tabName'!b" & irow) 'this line of code needs to paste whatever value is in the other sheet's cell
                'Range("'SUMMARY'!d" & orow) = Range("'tabName'!c" & irow) 'same objective as the last line of code, different rows and columns
            End If
        Next irow
    End If
Next ws

`Range("A" & irow)` needs to be qualified with the `Worksheet` in question. — BigBen, Oct 22 '20 at 18:28

VBA Double FOR loop with an IF statement

2 Answers2

Update Summary Worksheet