Solver iterations

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887
I thought my Solver would give me the end result by just running it once but I noticed that I need to run this code (without the for loop in it) at least 5 times to get the best results.

Is there a reason why I have to run this several times? My workaround was the for loop equating to clicking the command button 10 times

For i = 1 To 10

SolverOk SetCell:="$B$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$16:$I$16"
SolverOptions MaxTime:=100, Iterations:=1000, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.001, AssumeNonNeg:=False
SolverOk SetCell:="$B$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$16:$I$16"
'SolverSolve

SolverSolve UserFinish:=True

SolverFinish KeepFinal:=1

If i = 10 Then Exit Sub
Next
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
There could be really many reasons for that.
You don't give us enough information.
For example, how long does it take?
Also, what is the function you want minimize (or maximize?)
Also, I usually don't write VB code for using the solver, so I am not familiar with the VB names of the parameters. You would better explain in clear your settings.
Further, is your function continuous, which is very important.

...
 

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887
it takes a second or two to run the iterations, its also quick if you do it by using the solver form, but it stops again and again as opposed to running until it finds the optimal solution.

Not sure what you mean by function but I'm trying to reduce one cell's value to the mimimum using 3 lines of criteria. i.e.

Obtain the lowest cost to process widgests if more than one location can do it but the cost
by location are different based on about 5 metrics.

Does that help?
 

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
For "best results" answers, I would need the spreadsheet or at least all the details, specially the formulas, the datas, the constraints. I have seen no constraints in your macro.

Maybe you could check for the parameter IntTolerance.

Have you check for each iteration what the intermediate result is?

Are you sure all the functions are continuous?

Are all the function always well-behaved? Are there never a possible failure?

Can you be sure that the solver might not give a try to values that cannot be calculated, like the square roor of a negative number?

From your last post, if the cost is a simple linear combination, I could assume the problem is linear programming. If this is right, you could change the AssumeLinear parameter, and the problem should be solved without any iteration.

Have you checked that the final solution after 10 repetition is satisfying the constraint and is minimal?
Have you checked intermediate solutions, why do you assume there are not ok?

Really, you need to provide more information.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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