Looping with more than 1 array criteria

[cortexnotion]

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

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

 

[Fluff]

You could try

   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]

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

 

[Fluff]

You're welcome & thanks for the feedback.