开发者

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)" .

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜