Loop for a Complex Formula
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)
"gocush" /delete wrote in message
...
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
"monir" wrote:
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.
|