While the solver works fine when run manually,
Just a guess of course: Excel 2000?
The Solver add-in may not work when you start Solver by using a macro in
Excel 2000
http://support.microsoft.com/kb/821430/en-us
Just some ideas:
You have SolverOk listed twice. I'd drop the last one.
You can drop ValueOf, since it's ignored.
ie: SolverOk SetCell:="$BK$21", MaxMinVal:=1, ByChange:= "$BQ$17:$BQ$19"
Are you trying to make Q17:Q19 vary between 0 & 1, or make them either 0 or
1?
I'll assume "vary between 0-1" as posted.
I'm only guessing here on your model, so I may be wrong.
You want x+y+z = 1.
You want Solver to change x,y,z, with the constraint that they total 1.
As Solver changes x&y, it's almost impossible for Solver to guess z that
will make the equation sum to 1. Added to the problem is that fact that any
Sum will most likely not trigger a True for equality. (x+y+z = 1
....exactly! )
Also, as Solver gets close to guessing z, x&y get changed again, and Solver
gets confused.
On technique to work around this is to try to avoid equality functions.
You can do this here, for example, by solving for z.
With x+y+z =1, then z = 1-x-y.
So, have Solver change x & y.
Make z a function now with: =1-x-y.
Constraints are
x,y,z =0
x,y,z <=1.
Again, just some guesses.
--
HTH :)
Dana DeLouis
Windows XP & Excel 2007
wrote in message
ups.com...
Hey everyone,
I am encountering a problem while running the following excel solver
macro which I recorded:
Sub Macro1()
SolverReset
SolverOk SetCell:="$BK$21", MaxMinVal:=1, ValueOf:="0", ByChange:=
_
"$BQ$17:$BQ$19"
SolverAdd CellRef:="$BQ$17:$BQ$19", Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$BQ$17:$BQ$19", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$BQ$20", Relation:=2, FormulaText:="1"
SolverOk SetCell:="$BK$21", MaxMinVal:=1, ValueOf:="0", ByChange:=
_
"$BQ$17:$BQ$19"
SolverSolve
End Sub
While the solver works fine when run manually, the macro does not
arrive at the correct result. Looking up solver after the macro run
unveils that only one of the constraints (the second one) is loaded
into solver by the macro. The two other constraints miraculously
disappear. Any suggestions what I might do to fix this problem? I
really need it to work as it forms part of my master thesis :-(
Thank you very much in advance!
David