# Looping with more than 1 array criteria

#### cortexnotion

##### Board Regular
Hi All

Below is an extract from my code. Is there a way of looping through more than 1 criteria? I'm currently looping through the Week down the column. Is there a way of looping across the row with the 2nd criteria? Many thanks

VBA Code:
``````With Sheets("FigWork")
LR2 = .Range("A1").Row + 1
For Each Week In Array(WK1, WK2, WK3, WK4, WK5, WK6)
.Range("A" & LR2).Value = Week
.Range("B" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & FinCompPcRng & "))"
.Range("C" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & FinCompNoRng & "))"
.Range("D" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & FinCompValRng & "))"
.Range("E" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & TimeCompPcRng & "))"
.Range("F" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & TimeCompNoRng & "))"
.Range("G" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & TimeCompValRng & "))"
.Range("H" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & SubCompPcRng & "))"
.Range("I" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & SubCompNoRng & "))"
.Range("J" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & SubCompValRng & "))"
.Range("K" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & QACompPcRng & "))"
.Range("L" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & QACompNoRng & "))"
.Range("M" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & QACompValRng & "))"
.Range("N" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & CrtCompPcRng & "))"
.Range("O" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & CrtCompNoRng & "))"
.Range("P" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & CrtCompValRng & "))"
.Range("Q" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & ApprCompPcRng & "))"
.Range("R" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & ApprCompNoRng & "))"
.Range("S" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & ApprCompValRng & "))"
.Range("T" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & CollCompPcRng & "))"
.Range("U" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & CollCompNoRng & "))"
.Range("V" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & CollCompValRng & "))"
.Range("W" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & CollApprCompPcRng & "))"
.Range("X" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & CollApprCompNoRng & "))"
.Range("Y" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & CollApprCompValRng & "))"
.Range("Z" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & DataApprCompPcRng & "))"
.Range("AA" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & DataApprCompNoRng & "))"
.Range("AB" & LR2).Formula = "=SUMPRODUCT((" & WeekRng & "=" & Week & ")*(" & DataApprCompValRng & "))"
LR2 = LR2 + 1
Next Week
End With``````

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Fluff

##### MrExcel MVP, Moderator
You could try
VBA Code:
``````   Dim i As Long
Dim Ary As Variant
Ary = Array(FinCompPcRng, FinCompNoRng, FinCompValRng)
With Sheets("FigWork")
LR2 = .Range("A1").Row + 1
For Each week In Array(WK1, WK2, WK3, WK4, WK5, WK6)
.Range("A" & LR2).Value = week
For i = 0 To UBound(Ary)
.Range("B" & LR2).Offset(, i).Formula = "=SUMPRODUCT((" & WeekRng & "=" & week & ")*(" & Ary(i) & "))"
Next i
LR2 = LR2 + 1
Next week
End With``````

#### cortexnotion

##### Board Regular
Brilliant, thank you @Fluff. It makes sense but I couldn't see how to do it myself!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
3
Views
130
Replies
7
Views
106
Replies
43
Views
1K
Replies
4
Views
503
Replies
4
Views
681

### Forum statistics

1,129,673
Messages
5,637,717
Members
416,981
Latest member
PLonchar ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back