VBA Code for Solver
I am writing a simple program to use solver to asnwer a question. The user
interface will be on a spreadsheet, and I would like the user to adjust certain parameters, then click a macro button which will call up solver and "solve the problem". The problem is, I am trying to enable the user to adjust the valueof parameter in solverok by changing a cell on the spreadsheet, then clicking the button to get the answer. Unfortunately, that "valueof" parameter does not appear to take a cell location. Can someone suggest a simple workaround to make this work. Here is the macro code i am using now. SolverReset SolverAdd cellref:="$I$4", relation:=1, formulatext:="$D$4/$D$2" SolverAdd cellref:="$I$4:$I$8", relation:=3, formulatext:="0" SolverAdd cellref:="$I$4", relation:=4, formulatext:="integer" SolverAdd cellref:="$I$5", relation:=1, formulatext:="$D$5/$D$2" SolverAdd cellref:="$I$5", relation:=4, formulatext:="integer" SolverAdd cellref:="$I$6", relation:=1, formulatext:="$D$6/$D$2" SolverAdd cellref:="$I$6", relation:=4, formulatext:="integer" SolverAdd cellref:="$I$7", relation:=1, formulatext:="$D$7/$D$2" SolverAdd cellref:="$I$7", relation:=4, formulatext:="integer" SolverAdd cellref:="$I$8", relation:=1, formulatext:="$D$8/$D$2" SolverAdd cellref:="$I$8", relation:=4, formulatext:="integer" SolverAdd cellref:="$I$4", relation:=3, formulatext:="($D$4/$D$2)-4" SolverOk SetCell:="$J$9", MaxMinVal:=3, valueof:=i, ByChange:="$I$4:$I$8" SolverSolve Userfinish:=True SolverFinish Keepfinal:=1 |
VBA Code for Solver
Hi David,
I have used the Solver a few times, so I may not know all the finer tricks. However, when I do use the Solver, I use the constraints as my inputs to change the output. Is this your basic question? If so, set the constraint values to specific cells in the worksheet, change the values accordingly, and call up the Solver. The Solver should recalucatue based on the new constraints. Hopes this helps. RichardG "David" wrote in message ... I am writing a simple program to use solver to asnwer a question. The user interface will be on a spreadsheet, and I would like the user to adjust certain parameters, then click a macro button which will call up solver and "solve the problem". The problem is, I am trying to enable the user to adjust the valueof parameter in solverok by changing a cell on the spreadsheet, then clicking the button to get the answer. Unfortunately, that "valueof" parameter does not appear to take a cell location. Can someone suggest a simple workaround to make this work. Here is the macro code i am using now. SolverReset SolverAdd cellref:="$I$4", relation:=1, formulatext:="$D$4/$D$2" SolverAdd cellref:="$I$4:$I$8", relation:=3, formulatext:="0" SolverAdd cellref:="$I$4", relation:=4, formulatext:="integer" SolverAdd cellref:="$I$5", relation:=1, formulatext:="$D$5/$D$2" SolverAdd cellref:="$I$5", relation:=4, formulatext:="integer" SolverAdd cellref:="$I$6", relation:=1, formulatext:="$D$6/$D$2" SolverAdd cellref:="$I$6", relation:=4, formulatext:="integer" SolverAdd cellref:="$I$7", relation:=1, formulatext:="$D$7/$D$2" SolverAdd cellref:="$I$7", relation:=4, formulatext:="integer" SolverAdd cellref:="$I$8", relation:=1, formulatext:="$D$8/$D$2" SolverAdd cellref:="$I$8", relation:=4, formulatext:="integer" SolverAdd cellref:="$I$4", relation:=3, formulatext:="($D$4/$D$2)-4" SolverOk SetCell:="$J$9", MaxMinVal:=3, valueof:=i, ByChange:="$I$4:$I$8" SolverSolve Userfinish:=True SolverFinish Keepfinal:=1 |
All times are GMT +1. The time now is 03:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com