how to reference cells from sheet in vba for solveroptions

[breezer123]

Hi all! Thank you for taking the time to read my question and potentially offer some help!

I am trying to call solver from within a macro in VBA. I would like some of the solveroptions to be user inputs from the sheet. For example, saying solveroptions maxtime:="$J$3" where $J$3 = 1000 instead of having to put the value directly inside of vba solveroptions maxtime:=1000. Is this possible? I am getting a mismatch error on the first solveroptions line. Here is the code.

Sub solvermacro()

Application.ScreenUpdating = False
Application.ThisWorkbook.Sheets("Calculations").Activate


SolverOk SetCell:="$AL$79", MaxMinVal:=1, ValueOf:=0, ByChange:="$J$9:$J$39", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$AL$45:$AL$76", Relation:=3, FormulaText:="$J$2"
SolverAdd CellRef:="$J$9:$J$39", Relation:=1, FormulaText:="1.25"
SolverAdd CellRef:="$J$9:$J$39", Relation:=3, FormulaText:="0.75"


SolverOptions MaxTime:="$J$3", Iterations:="$J$4", Precision:=0.1, Convergence:= _
        0.001, StepThru:=False, Scaling:=False, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=0, RandomSeed:=0, MutationRate:=0.075, Multistart:= _
    False, RequireBounds:=False, MaxSubproblems:=0, MaxIntegerSols:=0, IntTolerance _
    :=1, SolveWithout:=False, MaxTimeNoImp:=30



solversolve
 
End Sub

 

[Worf]

This example uses cells from column K:

Sub Macro1()
SolverOk SetCell:="$D$6", MaxMinVal:=[k2], ValueOf:=[k3], _
ByChange:="$B$3:$B$5", Engine:=[k4], EngineDesc:="Simplex LP"
solveroptions maxtime:=[k5]
SolverSolve
End Sub