# Solver iterations

#### jmersing

##### Well-known Member
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
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
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
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?

Replies
0
Views
44
Replies
2
Views
264
Replies
1
Views
340
Replies
1
Views
756
Replies
1
Views
448

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.

### Which adblocker are you using?

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

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