Solver VBA doesn't apply all constraints

%3CLINGO-SUB%20id%3D%22lingo-sub-1406464%22%20slang%3D%22en-US%22%3ESolver%20VBA%20doesn't%20apply%20all%20constraints%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1406464%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20following%20VBA%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESolverReset%0ASolverOk%20SetCell%3A%3D%22%24C%2427%22%2C%20MaxMinVal%3A%3D1%2C%20ValueOf%3A%3D0%2C%20ByChange%3A%3D%22%24C%2422%3A%24C%2424%22%2C%20_%0AEngine%3A%3D1%2C%20EngineDesc%3A%3D%22GRG%20Nonlinear%22%0ASolverAdd%20CellRef%3A%3D%22%24C%2424%22%2C%20Relation%3A%3D1%2C%20FormulaText%3A%3D%22(%24C%2422%2B%24C%2423)*2%22%0ASolverAdd%20CellRef%3A%3D%22%24C%2430%22%2C%20Relation%3A%3D1%2C%20FormulaText%3A%3D%223%22%0ASolverAdd%20CellRef%3A%3D%22%24C%2430%22%2C%20Relation%3A%3D3%2C%20FormulaText%3A%3D%221%22%0ASolverAdd%20CellRef%3A%3D%22%24C%2422%22%2C%20Relation%3A%3D1%2C%20FormulaText%3A%3D%2212%22%0ASolverAdd%20CellRef%3A%3D%22%24C%2423%22%2C%20Relation%3A%3D1%2C%20FormulaText%3A%3D%223%22%0ASolverAdd%20CellRef%3A%3D%22%24C%2424%22%2C%20Relation%3A%3D3%2C%20FormulaText%3A%3D%220%22%0ASolverAdd%20CellRef%3A%3D%22%24C%2422%22%2C%20Relation%3A%3D4%2C%20FormulaText%3A%3D%22integer%22%0ASolverAdd%20CellRef%3A%3D%22%24C%2423%22%2C%20Relation%3A%3D4%2C%20FormulaText%3A%3D%22integer%22%0ASolverAdd%20CellRef%3A%3D%22%24C%2424%22%2C%20Relation%3A%3D4%2C%20FormulaText%3A%3D%22integer%22%0ASolverOk%20SetCell%3A%3D%22%24C%2427%22%2C%20MaxMinVal%3A%3D1%2C%20ValueOf%3A%3D0%2C%20ByChange%3A%3D%22%24C%2422%3A%24C%2424%22%2C%20_%0AEngine%3A%3D1%2C%20EngineDesc%3A%3D%22GRG%20Nonlinear%22%0ASolverSolve%20userFinish%3A%3DTrue%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20I%20run%20it%20the%20solver%20doesn't%20apply%20the%20constraint%20C30%20%26gt%3B%3D%201%2C%20and%20when%20I%20open%20up%20the%20solver%20dialog%20to%20see%20the%20constraints%20used%20it%20is%20missing%20from%20the%20solver.%3C%2FP%3E%3CP%3EIf%20the%20constraint%20is%20then%20added%20in%20the%20dialog%20it%20does%20get%20solved%20properly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1406464%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I have the following VBA code:

 

SolverReset
SolverOk SetCell:="$C$27", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$22:$C$24", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$C$24", Relation:=1, FormulaText:="($C$22+$C$23)*2"
SolverAdd CellRef:="$C$30", Relation:=1, FormulaText:="3"
SolverAdd CellRef:="$C$30", Relation:=3, FormulaText:="1"
SolverAdd CellRef:="$C$22", Relation:=1, FormulaText:="12"
SolverAdd CellRef:="$C$23", Relation:=1, FormulaText:="3"
SolverAdd CellRef:="$C$24", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$C$22", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$C$23", Relation:=4, FormulaText:="integer"
SolverAdd CellRef:="$C$24", Relation:=4, FormulaText:="integer"
SolverOk SetCell:="$C$27", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$22:$C$24", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve userFinish:=True

 

when I run it the solver doesn't apply the constraint C30 >= 1, and when I open up the solver dialog to see the constraints used it is missing from the solver.

If the constraint is then added in the dialog it does get solved properly.

2 Replies

@JoelF514 I'm having a similar issue.  Were you able to find a resolution?

@TSDutton   Similar how?  That's rhetorical.  The point is:  describe __your__ problem.

 

And I suspect that you really don't care if @JoelF514  per se found a solution.  Instead, you would like someone -- anyone -- to solve __your__ problem.

 

When you describe your problem, don't make the mistake that @JoelF514  did.  Be sure to provide sufficient information that allows us to reproduce your problem.  (FYI, I could not duplicate the problem with the limited informtion that @JoelF514   provided.)  Usually, that means all formulas in any cell that the objective cell depends on, directly or indirectly.

 

The best and perhaps simplest way to do that is by attaching an example Excel file (redacted) that demonstrates your problem.

 

Finally, be sure to let us know what version of Excel you are using, and on what platform (PC or Mac).  And if you are using Office 365, my understanding is:  it is useful to know the "channel" and the build version.