Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver macro
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver macro
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 -- MrShort ----------------------------------------------------------------------- MrShorty's Profile: http://www.excelforum.com/member.php...fo&userid=2218 View this thread: http://www.excelforum.com/showthread.php?threadid=38641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to run Solver using a macro? | Excel Worksheet Functions | |||
SOLVER macro | Excel Programming | |||
Solver Macro | Excel Programming | |||
Using Solver within Macro | Excel Programming | |||
Solver Macro | Excel Programming |