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
(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