Another solver question

[Greg Snidow]

Ok, I answered my last question, but I have another one, so I'm going to post
a new thread, since it's not really related. I have the following.

AddIns("Solver Add-in").Installed = True
SolverReset
SolverOk SetCell:="$N$7", MaxMinVal:=2, ValueOf:="0",
ByChange:="$B$8:$M$8"
SolverAdd CellRef:="$N$3:$N$7", Relation:=1, FormulaText:="$P$3:$P$7"
SolverAdd CellRef:="$N$8", Relation:=2, FormulaText:="$P$8"
SolverOk SetCell:="$N$7", MaxMinVal:=2, ValueOf:="0",
ByChange:="$B$8:$M$8"
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=True
SolverOk SetCell:="$N$7", MaxMinVal:=2, ValueOf:="0",
ByChange:="$B$8:$M$8"
SolverSolve userfinish:=True

Now, I have a situation where I will have to run solver multiple times
against the same data set, but with changing target values. So my question
is, at what point in the macro does solver know that it either can or cannot
solve the problem? What I want to happen is write some code to name my
initial target values, then run solver, and if a solution is found, save the
entire problem, not just the solution row, then reset solver, reset the
target values, and start all over again. What I am missing is how to
determine when solver knows its ready to give me an answer. There is some
good help in the VBA help menu, but I can't seem to answer this question. In
a nut shell, the loop will be like this.

1) Set initial target values.
2) Run solver with above macro
3) If solution is found, save it, if no sulution, reset solver, set next set
of target values.
4) Do until all target values have been tried.

I need help with #3 above. Thank you.

Greg.

 

[Greg Snidow]

Ok, I found the answer shortly after I posted again. It was here...
http://peltiertech.com/Excel/SolverVBA.html#Solver1

Specifically with this line of code...

Result = Application.Run("Solver.xla!SolverSolve", True)

and this table of result descriptions...

0 Solver found a solution. All constraints and optimality conditions are
satisfied. 1 Solver has converged to the current solution. All constraints
are satisfied. 2 Solver cannot improve the current solution. All constraints
are satisfied. 3 Stop chosen when the maximum iteration limit was reached. 4
The Set Cell values do not converge. 5 Solver could not find a feasible
solution. 6 Solver stopped at user's request. 7 The conditions for Assume
Linear Model are not satisfied. 8 The problem is too large for Solver to
handle. 9 Solver encountered an error value in a target or constraint cell.
10 Stop chosen when maximum time limit was reached. 11 There is not enough
memory available to solve the problem. 12 Another Excel instance is using
SOLVER.DLL. Try again later. 13 Error in model. Please verify that all cells
and constraints are valid.

Read more: http://peltiertech.com/Excel/SolverVBA.html#Solver1#ixzz0RCeLRiDr