# 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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### 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
501
Replies
4
Views
681

Threads
1,129,498
Messages
5,636,665
Members
416,935
Latest member
Atulcp

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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