G
Guest
I wrote the following code to quickly obtain optimum values using Solver.
The code is below.
Sub quikSolv(rngSolver_Target As Range, rngSolver_Output As Range,
iMaxMinVal As Integer)
' This macro uses SOLVER to set the traget cell to zero by changing the
output cell
SolverReset
SolverOk SetCell:=rngSolver_Target.Address, MaxMinVal:=iMaxMinVal,
ValueOf:="0", _
ByChange:=rngSolver_Output.Address
SolverAdd CellRef:=rngSolver_Output.Address, Relation:=3, FormulaText:="0"
SolverSolve True
End Sub
To make my code and spreadsheets easier to read, I am using six named ranges
in other routines, and I am sending them to the above macro. However, even
though I am using the Range.Address property to assign the Solver parameters,
Solver keeps changing the address to the string of the named range. Then, it
fails to optimize, saying "Set Target Cell contents must be a formula."
Is there any way around this?
Thanks,
Pflugs
The code is below.
Sub quikSolv(rngSolver_Target As Range, rngSolver_Output As Range,
iMaxMinVal As Integer)
' This macro uses SOLVER to set the traget cell to zero by changing the
output cell
SolverReset
SolverOk SetCell:=rngSolver_Target.Address, MaxMinVal:=iMaxMinVal,
ValueOf:="0", _
ByChange:=rngSolver_Output.Address
SolverAdd CellRef:=rngSolver_Output.Address, Relation:=3, FormulaText:="0"
SolverSolve True
End Sub
To make my code and spreadsheets easier to read, I am using six named ranges
in other routines, and I am sending them to the above macro. However, even
though I am using the Range.Address property to assign the Solver parameters,
Solver keeps changing the address to the string of the named range. Then, it
fails to optimize, saying "Set Target Cell contents must be a formula."
Is there any way around this?
Thanks,
Pflugs