I have the following problem: I need to minimize a cell by varying another cell value, which need to be an integer. My Excel Solver works ok sometimes, and other times it does not solve it. I noticed that it depends on the initial conditions, so according to the initial variable cell value, the solver might find a solution which is not optimum.
Any idea about it?
attached you find the piece of VBA code which runs the solver.
Sub optimizer1()
optimizer1 Macro
SolverReset 'resetting solver
SolverOk SetCell:="$AD$4", MaxMinVal:=2, ValueOf:=0, ByChange:="$Q$4", Engine:= _
1, EngineDesc:="GRG Nonlinear" 'setting up target and input cell
SolverAdd CellRef:="$Q$4", Relation:=4, FormulaText:="integer" 'The value of variable cell must be integer
'SolverAdd CellRef:="$U$4", Relation:=1, FormulaText:="$L$4" 'setting up the range (these are the boundary conditions)
'SolverAdd CellRef:="$U$4", Relation:=3, FormulaText:="$K$4"
'SolverAdd CellRef:="$AA$4", Relation:=1, FormulaText:="$L$4"
'SolverAdd CellRef:="$AA$4", Relation:=3, FormulaText:="$K$4" '(some Boundary conditions have been disabled, please neglect them for the purpose of the question)
SolverOk SetCell:="$AD$4", MaxMinVal:=2, ValueOf:=0, ByChange:="$Q$4", Engine:= _
1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$Q$4", Relation:=3, FormulaText:="$O$4"
SolverOk SetCell:="$AD$4", MaxMinVal:=2, ValueOf:=0, ByChange:="$Q$4", Engine:= _
1, EngineDesc:="GRG Nonlinear" ' Another boundary condition
SolverOk SetCell:="$AD$4", MaxMinVal:=2, ValueOf:=0, ByChange:="$Q$4", Engine:= _
1, EngineDesc:="GRG Nonlinear"
SolverSolve UserFinish:=True
End Sub
Thank you!