VBA Code for Solver

[Guest]

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

 

[RichardG]

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