带IF语句的VBA Double FOR循环 [英] VBA Double FOR loop with an IF statement

问题描述

我需要一个VBA脚本，它将执行一个双FOR循环.第一个FOR循环是一个需要在多张纸上执行一些命令的循环(第一张纸是主纸，需要跳过！)

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!!)

第二个for循环需要比较多行上的值.到目前为止，我已经粘贴了代码...

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.

推荐答案

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

这篇关于带IF语句的VBA Double FOR循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！