Solver macro

[TobP]

I am trying to write a macro that uses Solver. The solver is set to Set
Target Cell Equal To a Value Of (as opposed to a max or min value).

I can not work out a way to let the Value Of figure be referenced to
another cell, rather than just one, predetermined, value. Is this
possible? If so, how do I do it?

Any help much appreciated!

Extract from unsuccessful macro shown below:

(ValueOf:=Range("$F$28") is the bit that doesn't work)


SolverOk SetCell:=Range("$F$6"), MaxMinVal:=3,
ValueOf:=Range("$F$28"), ByChange:=Range("$B$2:$B$4")
SolverAdd CellRef:=Range("$B$3"), Relation:=1, FormulaText:="7"
SolverAdd CellRef:=Range("$B$3"), Relation:=3, FormulaText:="1.01"
SolverAdd CellRef:=Range("$B$4"), Relation:=1,
FormulaText:="-0.001"
SolverAdd CellRef:=Range("$B$4"), Relation:=3,
FormulaText:="-0.065"
SolverAdd CellRef:=Range("$D$21"), Relation:=2,
FormulaText:=Range("$D$24")
SolverOk SetCell:=Range("$F$6"), MaxMinVal:=3,
ValueOf:=Range("$F$28"), ByChange:=Range("$B$2:$B$4")
SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.01,
AssumeLinear:= _
False, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.001,
AssumeNonNeg:=False
SolverOk SetCell:=Range("$F$6"), MaxMinVal:=3,
ValueOf:=Range("$F$28"), ByChange:=Range("$B$2:$B$4")
SolverSolve
End Sub

 

[Dana DeLouis]

I can not work out a way to let the Value Of figure be referenced to

another cell, rather than just one, predetermined, value. Is this
possible? If so, how do I do it?

Hi. I don't think that is possible. If you run Solver manually, you will
notice that you can not point to a cell in the "Value of" box. Therefore, I
don't believe you can do this with a macro.
It may be done this way to make sure that the Target cell is not a dependent
cell that changes.

 

[MrShorty]

My usual workaround for this issue is to set up another cell (say G2
for your example) and put the formula =f6-f28 in that cell. Then you
Solver criteria becomes 'set cell G28 to a value of 0