EXCEL VBA Run Solver Over Multiple rows Using a Loop

Question

Hi All: I am completely brand new to VBA, so I used a macro recorder to help get a gist of the code I want. All of my inputs into Solver are in one row (e.g. row 19 of my worksheet). My objective cell is in column R, the decision cells are in columns E and F and the constraint is in column G. I eventually want to create more input rows, say 10, with 10 objective functions all in column R etc... I want to keep the columns fixed but create a loop that loops over the 10 rows, running solver for each row and then resets and runs the next row. When it reaches row 10 it checks to see if $R29 is "" if so it stops running.

I know that I probably should use some form of "Do While Cells(Row,1) <> "" " contruction, but I am at a losts how to create this simple loop in VBA. Thank you

    Sub RR_SC_OPTIMIZER()
'
' RR_SC_OPTIMIZER Macro
'

'
    SolverOk SetCell:="$R$19", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$19:$F$19", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$E$19", Relation:=1, FormulaText:="$G$19"
    SolverOk SetCell:="$R$19", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$19:$F$19", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$R$19", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$19:$F$19", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve
    SolverOk SetCell:="$R$19", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$19:$F$19", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
End Sub

Answer 1

You can try the following script (untested):

Sub RR_SC_OPTIMIZER()

    Dim rngObjectCells As Range
    Set rngObjectCells = Range("R19:R28")

    Dim rngObjectCell As Range

    For Each rngObjectCell In rngObjectCells

        SolverReset
        SolverOk SetCell:=rngObjectCell.Address, MaxMinVal:=2, ValueOf:=0, ByChange:=rngObjectCell.Offset(0, -13).Range("A1:B1").Address, _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverAdd CellRef:=rngObjectCell.Offset(0, -13).Address, Relation:=1, FormulaText:=rngObjectCell.Offset(0, -11).Address
        SolverOk SetCell:=rngObjectCell.Address, MaxMinVal:=2, ValueOf:=0, ByChange:=rngObjectCell.Offset(0, -13).Range("A1:B1").Address, _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverOk SetCell:=rngObjectCell.Address, MaxMinVal:=2, ValueOf:=0, ByChange:=rngObjectCell.Offset(0, -13).Range("A1:B1").Address, _
            Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve
        SolverOk SetCell:=rngObjectCell.Address, MaxMinVal:=2, ValueOf:=0, ByChange:=rngObjectCell.Offset(0, -13).Range("A1:B1").Address, _
            Engine:=1, EngineDesc:="GRG Nonlinear"

    Next

End Sub

Basically you are looping through your hardcoded range R19:R28 (not R29 as this would make it 11 Rows) and with each value in column R you are running a Solver. All the references in the solver are now based on the cell your are looping through. Hope this serves your purpose. Regards,