Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Solver Macro: Contraints Get Lost! - Please Help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Solver Macro: Contraints Get Lost! - Please Help
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Solver Macro: Contraints Get Lost! - Please Help
Hey Dana,
thank you very, very much for your suggestions! Although your first guess about Excel 2000 was wrong (actually using Excel 2003), your second suggestion did the trick. Thank you once again. David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Solver Macro: Contraints Get Lost! - Please Help
second suggestion did the trick.
Hi. Glad it's working. :) Nothing like a good guess. For my own education, was the solution the dropping the second SolverOk? The reason I ask is that others have also posted code where there were two SolverOk's. They also reported that they "recorded a macro" to get the code. If so, I wonder if there is something that triggers an error with the recording of Solver macros? -- Dana DeLouis wrote in message ups.com... Hey Dana, thank you very, very much for your suggestions! Although your first guess about Excel 2000 was wrong (actually using Excel 2003), your second suggestion did the trick. Thank you once again. David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Solver Macro: Contraints Get Lost! - Please Help
Dana -
Whenever I record a macro using Solver, if it records setting of constraints it puts SolverOK both before and after the constraints. I usually delete the first, but just to streamline the code. I haven't noticed any issue with running the code with either or both SolverOK statements, it just seemed wasteful to have both, and seemed to be logical (like that should matter) to put SolverOK after the SolverAdd's. FYI, I recently posted a page on using Solver with VBA, and I'd appreciate any suggestions from someone with your knowledge about Solver. If you have any comments, use the email link at the bottom of the page: http://peltiertech.com/Excel/SolverVBA.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Dana DeLouis" wrote in message ... second suggestion did the trick. Hi. Glad it's working. :) Nothing like a good guess. For my own education, was the solution the dropping the second SolverOk? The reason I ask is that others have also posted code where there were two SolverOk's. They also reported that they "recorded a macro" to get the code. If so, I wonder if there is something that triggers an error with the recording of Solver macros? -- Dana DeLouis wrote in message ups.com... Hey Dana, thank you very, very much for your suggestions! Although your first guess about Excel 2000 was wrong (actually using Excel 2003), your second suggestion did the trick. Thank you once again. David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver Binary Contraints problem | Excel Worksheet Functions | |||
SUMIF with two contraints? | Excel Worksheet Functions | |||
Sheets Looping ignoring contraints | Excel Programming | |||
excel solver macro | Excel Programming | |||
macro using excel solver | Excel Programming |