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.

--
Dana DeLouis
Win XP & Office 2003


"TobP" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> TobP
> ------------------------------------------------------------------------
> TobP's Profile:
> http://www.excelforum.com/member.php...o&userid=25149
> View this thread: http://www.excelforum.com/showthread...hreadid=386415
>

[MrShorty]

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