Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
Resetting Solver Manually to Fix Solver Bug | Excel Programming | |||
VBA code to add a reference to solver | Excel Programming | |||
Solver code does not save results to sheet - What am I missing?! | Excel Programming |