Search code examples
excelfor-loopnested-loopsvba

Simultaneously changing variables for loops VBA


Attempting to get BRarr1 and BRarr2 to both change at same time. I am aware that the way it runs currently it will change b before it will change a.

Is there a way to Run something like this so that both sheets change before proceeding through code?

for ex. I need to run through BR01 and BR01A then run through BR5 and BR05A ...etc

Sub BranchAnalysis()
Dim s1 As Worksheet
Dim s2 As Worksheet
Dim a As Integer
Dim b As Integer
Dim BRarr1
Dim BRarr2

BRarr1 = Array("BR1", "BR5", "BR10", "BR11", "BR13", "BR18", "BR20", "BR21", 
                "BR22", "BR23", "BR25", "BR28", "BR29", "BR33", "BR35")
BRarr2 = Array("BR01A", "BR05A", "BR10A", "BR11A", "BR13A", "BR18A", 
              "BR20A", "BR21A", "BR22A", "BR23A", "BR25A", "BR28A", "BR29A", 
               "BR33A", "BR35A")

For a = LBound(BRarr1) To UBound(BRarr1)
    For b = LBound(BRarr2) To UBound(BRarr2)

    Set s1 = Workbooks("Primary to Network vBG2.xlsm").Worksheets("" & BRarr1(a) & "")
    Set s2 = Workbooks("Primary to Network vBG2.xlsm").Worksheets("" & BRarr2(b) & "")

    r1 = Workbooks("Primary to Network vBG2.xlsm").Worksheets("" & BRarr1(a) & "").UsedRange.Rows.count
    r2 = Workbooks("Primary to Network vBG2.xlsm").Worksheets("" & BRarr2(b) & "").UsedRange.Rows.count

    Dim i, j As Long

    'line items count
    Dim count1 As Long
    count1 = 0
    For j = 2 To r2
        For i = 2 To r1
        If s2.Cells(j, 1).Value = s1.Cells(i, 1).Value Then
           count1 = count1 + 1
           s2.Cells(j, 2).Value = count1
        Else
        End If
        Next i
        count1 = 0
    Next j

blah blah more code

    Next b
next a

Solution

  • (based on Scott's comment)

    Both BRarr1 and BRarr2 have ubound=14

    So only use one loop:

    Replace:

    For a = LBound(BRarr1) To UBound(BRarr1)
        For b = LBound(BRarr2) To UBound(BRarr2)
    

    with something like:

     For ZZ = 0 to 14
    

    and use ZZ wherever you used either a or b