# 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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### 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?

Really, you need to provide more information.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,080
Messages
5,835,246
Members
430,350
Latest member
Shuvam Senapati

### 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?

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