Excel Solver - Optimizing Using Changing “ValueOf”

Question

I'm trying to set up a macro that can parameterize an equation given 2 inputs. I cannot figure out a way to have Solver use the value of a cell as the "Value Of" though. The macro I've got at the moment is as follows:

Sub Macro2()

   SolverReset
   SolverAdd CellRef:="$G$2", Relation:=1, FormulaText:="100"
   SolverAdd CellRef:="$G$2", Relation:=3, FormulaText:="0"
   SolverOk SetCell:="$M$6", MaxMinVal:=3, ValueOf:="$B$2", ByChange:="$G$2", Engine:= _
       3, EngineDesc:="Evolutionary"
   SolverSolve

End Sub

When the macro is executed, the Solver Results box pops up saying Error in model. Please verify that all cells and Constraints are valid.

Is there any way to accomplish what I'd like to?

Answer 1

Nat.

Instead of directly referencing a cell for the "ValueOf" parameter, use a variable of the referenced cell. For example:

Sub Macro2()

    Dim target As Double
    target = Range("B2")

    SolverReset
    SolverAdd CellRef:="$G$2", Relation:=1, FormulaText:="100"
    SolverAdd CellRef:="$G$2", Relation:=3, FormulaText:="0"
    SolverOk SetCell:="$M$6", MaxMinVal:=3, ValueOf:=target, ByChange:="$G$2", _
      Engine:= 3, EngineDesc:="Evolutionary"
    SolverSolve

End Sub

Hope that addresses your issue.

Regards, Sal