Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop for a Complex Formula
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop for a Complex Formula
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop for a Complex Formula
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. "Bob Phillips" wrote: I think that should be "gocush" /delete wrote in message "monir" wrote: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex AND OR Formula | Excel Worksheet Functions | |||
Complex formula help | Excel Discussion (Misc queries) | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
need complex if formula (I think) | Excel Worksheet Functions | |||
Do---Loop Error in Complex Code | Excel Programming |