VBA Goalseek inside for loop

StianKvam

New Member
Joined
Feb 23, 2018
Messages
4
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.

Code:
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
Hello

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


Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,126,896
Messages
5,621,497
Members
415,844
Latest member
Reda Fouad Ramzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top