VBA: SolverAdd Ignorning Constraints
I'm trying to set up solver for each run of a macro but it seems to be ignoring some of the constraints but not all of them for some reason.
SolverReset
SolverOk SetCell:="$N$15", MaxMinVal:=3, ValueOf:=0, ByChange:=Range("i3", Range("i3").End(xlDown)), _
Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:=Range("k3", Range("k3").End(xlDown)), Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$P$12:$P$13", Relation:=1, FormulaText:="$R$12:$R$13"
SolverAdd CellRef:="$P$12:$P$13", Relation:=3, FormulaText:="$N$12:$N$13"
SolverAdd CellRef:=Range("n3", Range("n3").End(xlDown)), Relation:=2, FormulaText:="1"
SolverAdd CellRef:=Range("i3", Range("i3").End(xlDown)), Relation:=5, FormulaText:="binary"
SolverSolve
It is only ignoring the constraints for the ranges Range("n3", Range("n3").End(xlDown)) and Range("k3", Range("k3").En开发者_JAVA技巧d(xlDown))
Any help would be appreciated.
thanks!
From XL Help:
"Relation Required Integer. The arithmetic relationship between the left and right sides of the constraint. If you choose 4 or 5, CellRef must refer to adjustable (changing) cells, and FormulaText should not be specified."
Your last range has relation=5, so you can't include FormulaText.
I had same problem and FormulaText for range with relation=5 was not a problem, but problem was FormulaText="1". Every SolverAdd which had FormulaText="1" was ignored in solver. Solution for me was just to add "1" in one cell (e.g E9) and set FormulaText="$E$9". There is probably better solution, but this was fine for me...
There is probably better solution, but this Works fine for me :
SolverAdd CellRef:=Range("k3", Range("k3").End(xlDown)), Relation:=1, FormulaText:="SUM(1)"
just replace "1" with "SUM(1)" .
精彩评论