Loop incrementing formula

[hgus393]

Hi all,

I am trying to loop a formula in VBA, but can't wrap my head around how to do it.

The formula on the starting row is:

=SUM(($C$5:$C$9=$X$4)*SUM($C$5:$C$9=$X$4))

How can I increment the following rows so that the following rows will be like this:

=SUM((Starting point from above ie C10 :increment by 5 ie c14 =$X$4)*SUM(Starting point from above ie C10 :increment by 5 ie c14=$X$4))

Next row

=SUM((Starting point from above ie C15 :increment by 5 ie c19 =$X$4)*SUM(Starting point from above ie C15 :increment by 5 ie c19=$X$4))

and so on

Cheers

Rob

 

[Arul.rajesh]

You trying to use an array formula which would always return zero... IF you could explain what you are trying to do exactly, there might be a more elegant solution. Try looking at the address function and the indirect function..

 

[repairman615]

Hello,

Here is an VBA example that may help You:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> LoopExample()<br><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> intIncrement <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br><SPAN style="color:#00007F">Set</SPAN> Ws = ActiveSheet<br><br>i = 1<br>intIncrement = 0<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 5<br>        <SPAN style="color:#007F00">'''The below line refers to cell A2 as the starting row.</SPAN><br>        Ws.Range("A2").Offset(i, 0).Formula = _<br>        "=SUM(($C$5:$C$" & 9 + intIncrement & "=$X$4)*SUM($C$5:$C$" & 9 + intIncrement & "=$X$4))"<br>        intIncrement = intIncrement + 5<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[repairman615]

Whoops...

this corrects the starting on row 2 in this case:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> LoopExample()<br><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> intIncrement <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br><SPAN style="color:#00007F">Set</SPAN> Ws = ActiveSheet<br><br>i = 1<br>intIncrement = 0<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 5<br>        <SPAN style="color:#007F00">'''The below line refers to cell A2 as the starting row.</SPAN><br>        Ws.Range("A2").Offset(i - 1, 0).Formula = _<br>        "=SUM(($C$5:$C$" & 9 + intIncrement & "=$X$4)*SUM($C$5:$C$" & 9 + intIncrement & "=$X$4))"<br>        intIncrement = intIncrement + 5<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[hgus393]

Nice...how would below look like if the start row is also a variable that is pushed by 5 each time ie. "=SUM((C5....) next row "=SUM((C10...)) and so on?

/Rob

Sub LoopExample()

Dim i As Long
Dim intIncrement As Integer
Dim Ws As Worksheet

Set Ws = ActiveSheet

i = 1
intIncrement = 0
For i = 1 To 5
'''The below line refers to cell A2 as the starting row.
Ws.Range("A2").Offset(i - 1, 0).Formula = _
"=SUM(($C$5:$C$" & 9 + intIncrement & "=$X$4)*SUM($C$5:$C$" & 9 + intIncrement & "=$X$4))"
intIncrement = intIncrement + 5
Next i

End Sub

 

[repairman615]

Basically the same:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> LoopExample()<br><br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> intIncrement <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br><SPAN style="color:#00007F">Set</SPAN> Ws = ActiveSheet<br><br>i = 1<br>intIncrement = 0<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 5<br>        <SPAN style="color:#007F00">'''The below line refers to cell A2 as the starting row.</SPAN><br>        Ws.Range("A2").Offset(i - 1, 0).Formula = _<br>            "=SUM(($C$" & 5 + intIncrement & ":$C$" & 9 + intIncrement & "=$X$4)" & _<br>            "*SUM($C$" & 5 + intIncrement & ":$C$" & 9 + intIncrement & "=$X$4))"<br>        intIncrement = intIncrement + 5<br>    <SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[repairman615]

Is this an array formula? (Control + Shift + Enter)

 

[hgus393]

Excellent, you're a star!!

Cheers

Rob

 

[hgus393]

Yeah, I sussed that out instead of .formula, FormulaArray.

Cheers

Rob

Is this an array formula? (Control + Shift + Enter)

 

[repairman615]

Awesome. The FormulaArray was exactly needed!