Iterative calculation help

[beca]

Hi there

I have an equation that requires iterative calcualtion to get the desired value. what I have done is to rearrange the equation to put all the parameters on one side, and set the equation equal to zero, and use "goal seek" function to solve for the desired parameter.

Because I have to do the same thing for about 100 rows, I recorded the process in the macro, and the code looks like as below:

Range("F4").GoalSeek Goal:=0, ChangingCell:=Range("D4")

Then I copied the above function 100 time and changed the cell reference manually in the macro for the rest of the spreadsheet. This works fine, However, when I insert rows in the spreadsheet, I have to manually update the macro as well, which is very frustrating.

Can anyone help me to fully automate the process, so that it is still able to do iterative calculations once I insert a new row.

Thank you

 

[Sektor]

ChangingCell:=Range("[B]$[/B]D[B]$[/B]4")

 

[beca]

Sorry, That was a typo, it is supposed to be Range("$F$4").GoalSeek Goal:=0, ChangingCell:=Range("$D$4")

But it still doesn't solve the problem when i insert new rows in the spreadsheet.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
    For i = 4 To LastRow
        Range("F" & LastRow).GoalSeek Goal:=0, ChangingCell:=Range("D" & LastRow)
    Next i
End Sub

 

[beca]

Hi Andrew

Thank you for your reply.

I can see what you're trying to do in the code, but the problem is a summary table sits right after the last calculating row, if you set the dim to last row, it won't work, because the summary table has a different table set up from the calculating rows, and the summary table doesn't need iterative calculation either.

I can leave a spare row between the calculating table and the summary table if it can make the spreadsheet work.

Can you please modify the code to suit?

Thank you <!-- / message -->

 

[Andrew Poulsom]

Insert a row before the summary and try:

LastRow = Range("D4").End(xlDown).Row

 

[beca]

Thank you Andrew, it now works perfectly!!