Solver: no solution

Hi,

First of all, I'd like to say thanks to everyone here; I've picked up more on this forum than from any book...

I have constructed a macro that calls solver, enters constraints and solves the optimization problem. However, for some constraints there will be no solutions. Unfortunately, solver will give me the nearest value instead of stating N/A (which I would prefer). Is there a code to resolve this? Thanks in advance! (Below is my code, which I know is inefficient, but don't mind that, I'll work on that later).

Sub SOLVER()
'
' Solver Macro
' Macro recorded 30-5-2007 by
'
Application.ScreenUpdating = False

    SolverReset
    SolverOk SetCell:="$M$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$4:$H$4"
    SolverAdd CellRef:="$B$4", Relation:=3, FormulaText:="$N$30"
    SolverAdd CellRef:="$B$4", Relation:=1, FormulaText:="$O$30"
    SolverAdd CellRef:="$C$4", Relation:=3, FormulaText:="$N$31"
    SolverAdd CellRef:="$C$4", Relation:=1, FormulaText:="$O$31"
    SolverAdd CellRef:="$D$4", Relation:=3, FormulaText:="$N$32"
    SolverAdd CellRef:="$D$4", Relation:=1, FormulaText:="$O$32"
    SolverAdd CellRef:="$E$4", Relation:=3, FormulaText:="$N$34"
    SolverAdd CellRef:="$E$4", Relation:=1, FormulaText:="$O$34"
    SolverAdd CellRef:="$F$4", Relation:=1, FormulaText:="$O$33"
    SolverAdd CellRef:="$F$4", Relation:=3, FormulaText:="$N$33"
    SolverAdd CellRef:="$G$4", Relation:=1, FormulaText:="$O$33"
    SolverAdd CellRef:="$G$4", Relation:=3, FormulaText:="$N$33"
    SolverAdd CellRef:="$H$4", Relation:=1, FormulaText:="$O$34"
    SolverAdd CellRef:="$H$4", Relation:=3, FormulaText:="$N$34"
    SolverAdd CellRef:="$K$2", Relation:=2, FormulaText:="$J$4"
    SolverAdd CellRef:="$M$6", Relation:=2, FormulaText:="$M$9"
    SolverOk SetCell:="$M$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$4:$H$4"
    SolverSolve userFinish:=True

Thanks again, kind regards,

Peter1999

Interesting problem.

Without specifics of your spreadsheet model, I can only talk in generalities.

The first step in the solution is how to identify when a solution exists and when it doesn't. If there is a readily identifiable condition that you could test for after Solver has finished, that might be the easiest.

As an exercise, I made up my own spreadsheet that I could solve with Solver, then recorded a macro that setup the basic code.

VBA help calls "solversolve" a function, so I expected that it should return a value, and hopefully that value would be related to whether or not Solver had converged to a solution. I changed that line of code to

slv1=solversolve(true)'parentheses are needed around arguments when setting to variable

Sure enough, when Solver found a solution, it returned 0, when it couldn't find a feasible solution, it returned 5.

I don't know of anywhere where the return values for the solversolve function are documented, but I expect if you spent some time exploring your specific problem, you would be able to identify what return values represent "no solution" for you specific spreadsheet model. This assumes that Solver recognizes that it didn't come to a solution. I'm also assuming that the result from solversolve will be reasonably consistent from run to run.

Using one of those two approaches, you can then identify the "no solution" condition. Then, simply put NA where you want it to be.

If (no solution exists) then
celltomodify.value=cverr(xlerrna)
end if

The only thing to note in this snippet is that if celltomodify contains a formula, you will lose the formula when you set it to NA. If this is what you want, make sure you leave yourself a way to recover the formula the next time you need it.

Hope that helps.

Thanks, I'll give that a try

Peter1999