Loop FOR duplo VBA com uma instrução IF

Question

Preciso de um script VBA que execute um loop FOR duplo. O primeiro loop FOR é um loop que precisa executar alguns comandos em várias planilhas (a primeira planilha é a planilha principal e precisa ser pulada !!)

O segundo loop for precisa comparar valores em várias linhas. Eu colei meu código até agora ...

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

Atualmente, o código é executado, mas só está gerando resultados do primeiro (planilha principal). Ele precisa pular a primeira folha e percorrer as demais.

Answer 1

Atualizar planilha de resumo

Link: Função StrComp

• O seguinte não foi testado.

O código

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