Goal Seek in VBA with variables

[davewagner]

Trying to automate Goal Seek using variables for the three arguments. It's working when I define the cells references & values within the code, but cannot figure out how using variables. I'm sure the example below is incorrect, but will give an idea what I need to accomplish:

GoalCell = Worksheets("Parts").Cells(DestRowID + 10, DestColID)
Value = Worksheets("ICA").Cells(CopyRowID + 13, CopyColID).Value
ChangeCell= Worksheets("Parts").Cells(DestRowID + 9, DestColID)

GoalCell.GoalSeek Goal:=Value, ChangingCell:=ChangeCell

Any ideas?

 

[VoG]

Hello and welcome to MrExcel.

Try

Worksheets("Parts").Cells(DestRowID + 10, DestColID).GoalSeek _
goal:=Worksheets("ICA").Cells(CopyRowID + 13, CopyColID).Value, _
Changingcell:=Worksheets("Parts").Cells(DestRowID + 9, DestColID)

 

[davewagner]

Thanks a million VoG. Works perfectly.

 

[VoG]

See Step 4 here http://www.bettersolutions.com/excel/EAV113/VE848417571.htm

To be honest I'm not sure if this will affect the results from a VBA goalseek but no harm in trying (and please let us know for future reference).

Edit: I see you've modified your response. My reply above was to increase the precision of the goalseek.

 

[davewagner]

Sorry for the back/forth edits to the post - I mistakenly looked at some previous calculations. Precision is working properly when using the direct cell references (i.e. "E25") but not as precise when using variable cell references (i.e. "DestRowID + 10, DestColID").


I selected (Tools > Options)(Calculation tab).

Checked the Iteration box.

Set Maximum iterations = 10000

Set Maximum change = 0.00001

Saved the workbook, closed it, opened it, ran the code, same result.

Does not appear to have an affect, but it was worth a shot.

Thanks again for your help.

 

[VoG]

A couple of links to look at http://www.excelforum.com/excel-programming/376747-vba-goal-seek.html and http://forums.whirlpool.net.au/forum-replies-archive.cfm/1111319.html

 

[davewagner]

Both good links (had looked at before but didn't connect as the simple solution you provided above). Based on the precision issue, started investigating Solver. Using the following code:

SolverOptions MaxTime:=10000, Iterations:=10000, Precision:=0.00001, _
AssumeLinear:=False, StepThru:=False, Estimates:=0, Derivatives:=1, _
SearchOption:=1, IntTolerance:=5, Scaling:=True, Convergence:=0.000000001, _
AssumeNonNeg:=True

SolverOk SetCell:=Worksheets("Parts").Cells(DestRowID + 31, DestColID), _
MaxMinVal:=3, _
ValueOf:=Worksheets("ICA").Cells(CopyRowID + 13, CopyColID).Value, _
ByChange:=Worksheets("Parts").Cells(DestRowID + 30, DestColID)

SolverSolve UserFinish = False
SolverFinish KeepFinal:=1

When it runs, the following message is displayed via a dialog box with only an "OK" button:

"Solver: An unexpected internal error occurred, or available memory was exhausted."

Thoughts?

 

[VoG]

Try reducing the iterations and the precision. Unfortunately this will be an iterative procedure on your part

 

[davewagner]

If I run the solver manually and reduce the iterations and the precision, the proper answer is returned. I've tried the same thing in the code, but changed the settings so that it prompts me with the answer, and it states "Solver could not find a feasible solution."

Made some changes to the code to ensure that it matches the options defined in the manual solver, but still not luck.

SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, _
AssumeLinear:=False, StepThru:=False, Estimates:=0, Derivatives:=0, _
SearchOption:=0, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False

 

[VoG]

If I run the solver manually and reduce the iterations and the precision, the proper answer is returned. I've tried the same thing in the code, but changed the settings so that it prompts me with the answer, and it states "Solver could not find a feasible solution."

Made some changes to the code to ensure that it matches the options defined in the manual solver, but still not luck.

SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, _
AssumeLinear:=False, StepThru:=False, Estimates:=0, Derivatives:=0, _
SearchOption:=0, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False

OK, well you've amply demonstrated the limits of my abilities with either of these methods

I think that it would be within the forum rules to start a new thread about goalseek/solver in VBA precision since this is really a different question to the original. Anyway, I must bow out because I don't think that I can help further - sorry.