Loop formula help

[VbaHell]

Hello all

I need help please if possible on this loop formula
It works well but I want to modify this as follows

At the moment this loops the formula from row 31 to 40 but I need it to loop all the rows below

31 to 40
61 to 70
301 to 310
331 to 340
421 to 430
451 to 460
541 to 550
571 to 580
661 to 670
691 to 700
781 to 790
811 to 820
901 to 910
931 to 940

LC = Cells(11, Columns.Count).End(xlToLeft).Column

For aa = 31 To 40 Step 1


With Range(Cells(aa, 4), Cells(aa, LC))
.FormulaR1C1 = "=SUMIFS('MIS - Customer Invoice'!C11,'MIS - Customer Invoice'!C46,R30C,'MIS - Customer Invoice'!C8,RC2)"
'.Value = .Value
End With
Next aa‹

I currently have the code written for each of the above rows and was wondering if one loop but with some added STEP code would be more efficient

 

[Michael M]

what is in the "gaps"
My idea would put the formula in EVERY row, IF a criteria was met in another column
Something like if cells(aa,3) is NOT blank, or NOT equal to ....some criteria

For aa = 31 To 940 Step 1

if cells(aa,3)<>"" then
With Range(Cells(aa,4).Formula = "=SUMIFS('MIS - Customer Invoice'!C11,'MIS - Customer Invoice'!C46,R30C,'MIS - Customer Invoice'!C8,RC2)"
'.Value = .Value
End With
end if
Next aa

 

[ukmikeb]

Try :-

Dim Arr
Dim LC As Long, aa As Long, i As Long

LC = Cells(11, Columns.Count).End(xlToLeft).Column
Arr = Array(31, 61, 301, 331, 421, 451, 541, 571, 661, 691, 781, 811, 901, 931)

For i = LBound(Arr, 1) To UBound(Arr, 1) Step 1

    For aa = Arr(i) To Arr(i) + 9 Step 1

        With Range(Cells(aa, 4), Cells(aa, LC))
             .FormulaR1C1 = "=SUMIFS('MIS - Customer Invoice'!C11,'MIS - Customer Invoice'!C46,R30C,'MIS - Customer Invoice'!C8,RC2)"
            '.Value = .Value
        End With

    Next aa

Next i

which also gives you the flexibility to vary the ranges.

hth

 

[ukmikeb]

Or still with one loop :-

Dim Arr
Dim LC As Long, i As Long

LC = Cells(11, Columns.Count).End(xlToLeft).Column
Arr = Array(31, 61, 301, 331, 421, 451, 541, 571, 661, 691, 781, 811, 901, 931)

For i = LBound(Arr, 1) To UBound(Arr, 1) Step 1

        With Range(Cells(Arr(i), 4), Cells(Arr(i) + 9, LC))
             .FormulaR1C1 = "=SUMIFS('MIS - Customer Invoice'!C11,'MIS - Customer Invoice'!C46,R30C,'MIS - Customer Invoice'!C8,RC2)"
            '.Value = .Value
        End With

Next i

hth

 

[VbaHell]

Mike thank you so much for solving this issue for me, I have never used Lbound before

 

[ukmikeb]

You're welcome.

Thanks for the feedback.

There's a first time for everything .

Essentially the use of LBound/Ubound enables the array to be increased/reduced without having to worry how many elements are present.

Good luck with your project.