VBA Goalseek inside for loop

[StianKvam]

Hello everyone.

I'm fairly new to coding so bear with me.
I have written a small goalseek code inside a for next loop. When the code runs I get an error at the end, probably because some other values in the sheet goes to zero before goalseek can do all the calculations. The error is Run-time error 110041: Reference is not valid.

I want the code to clear contents in cells "AC", which by the way is the same cells as goalseek is changing.
This is because previously, I experience errors during the loop, it works well now with clearcontents before the loop.

One problem with this code though is that the end cell may vary. It doesn't need to be 115, For i = 11 to 115).
I'm not sure how to get code to understand that it should run until a certain criteria is met, for instance a certain value.
The column "AC" consists of decreasing temperatures, this is what I want the criteria to be, say the value 17.
As of right now the value 17 is found in row 81.

See code below and if someone could help it would be much appreciated.

Sub Reactor_Temp()

With Application
   
   .Iteration = True
   .MaxIterations = 10000
   .MaxChange = 0.001


End With


Range("AC11:AC115").ClearContents


Dim i As Long


For i = 11 To 115
   
    Range("AP" & i).GoalSeek Goal:=0, ChangingCell:=Range("AC" & i)
    
    
Next


End Sub

 

[Worf]

Hello

Note that you will have to change values, as my test model is different

Sub Reactor_Temp()
Dim i%, T#
T = 0.01                        ' limit temperature
With Application
    .Iteration = True
    .MaxIterations = 10000
    .MaxChange = 0.001
End With
[AC:AC].ClearContents
i = 10
Do
    i = i + 1
    Range("AP" & i).GoalSeek goal:=-900, ChangingCell:=Range("AC" & i)
Loop While Range("ac" & i) > T And i < 200
MsgBox "Last row is " & i
End Sub

 

[StianKvam]

Hi Worf.

Thanks for the help, it worked like a charm.