Loop for a Complex Formula

[Guest]

The following 3 statements are repeated few times in succession in a VBA
macro. The code works fine.

' for row index 12, and post result into cell Q12
Set myFormula = myFormula.Offset(, 1)
myFormula.Formula = "= C21*$D$12^D21*$E$12^E21*$F$12^F21* ... "
Range("Q12").Offset(0, 0).Formula = "=sum(" &
myFormula.Rows().Address(False, True) & ")"

' for row index 11, and post result into cell R11
Set myFormula = myFormula.Offset(, 1)
myFormula.Formula = "= C21*$D$11^D21*$E$11^E21*$F$11^F21* ... "
Range("Q12").Offset(-1, 1).Formula = "=sum(" &
myFormula.Rows().Address(False, True) & ")"

' for row index 10, and post result into cell S10
Set myFormula = myFormula.Offset(, 1)
myFormula.Formula = "= C21*$D$10^D21*$E$10^E21*$F$10^F21* ... "
Range("Q12").Offset(-2, 2).Formula = "=sum(" &
myFormula.Rows().Address(False, True) & ")"

and so on.

How can I modify the statement myFormula.Formuls so that I may include the 3
statements in a loop, changing only the row index from 12 to 11 to 10 to 9
etc. ?

Thank you kindly.

 

[Guest]

Does this help:

First, just to make it easier to read, I would change your variable
"myFormula" to "myRng" since it is a range

Then

Dim myRng as Range
Dim i as integer

For i = 12 to 9 Step -1 'adjust range as needed
Set myRng= myRng.Offset(, 1)
myRng.Formula = "= C21*$D$12^D21*$E$12^E21*$F$12^F21* ... "
Range("Q"& i).Formula = "=sum(" &
myRng.Rows().Address(False, True) & ")"

Next i

 

[Bob Phillips]

I think that should be

Dim myRng as Range
Dim i as integer

For i = 12 to 10 Step -1 'adjust range as needed
Set myRng= myRng.Offset(, 1)
myRng.Formula = "= C21*$D$12^D21*$E$" & i & "^E21*$F$" & i & "^F21* ...
"
Cells( i, i+5).Formula = "=sum(" & myRng.Rows().Address(False, True) &
")"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

It works! You need, however, to introduce a separate Counter for the
destination column since it is not related to the destination row index i.

Counter = 0
For i = 12 To 8 Step -1 'destination rows 12 to 8, destination cols
Q::17 to U::21
Counter = Counter + 1
Set myFormula = myFormula.Offset(, 1)
myFormula.Formula = "=C21*$D$" & i & "^D21*$E$" & i & "^E21*$F$" & i &
"^F21*... "
Cells(i, 16 + Counter).Formula = "=sum(" &
myFormula.Rows().Address(False, True) & ")"
Next i

Thank you.