# Loop formula help

#### VbaHell

##### Well-known Member
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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
Code:
``````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``````

Try :-
Code:
``````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

Or still with one loop :-
Code:
``````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

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

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.

Replies
3
Views
685
Replies
6
Views
813
Replies
3
Views
546
Replies
6
Views
2K
Replies
1
Views
602

1,203,224
Messages
6,054,234
Members
444,711
Latest member
Stupid Idiot

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