How to automate solver in VBA

Question

I know this questions has been asked a million times, but I'm still struggling to get it to work after combing through solutions.

I'm trying to maximize the value in column M by changing the variables in columns I through K with the constraints that 0<I:K>1 starting on row 9. I need this to reiterate for 330 rows. I'm using 10 as a proof that the code works over multiple rows and will extend it after I verify it.

It seems to be reiterate over multiple rows but something in the syntax causes an error in the model.

Error message I get:

Error in Model. Please verify that variables and constraints are valid. Perhaps some cells that are not variable cells are marked as integers, binary or all different.

I know basically nothing about VBA so please dumb it down and help me understand that syntax and format of the code corrections. Thank you,

Sub Macro2()

Dim i As Integer

For i = 0 To 10

    Sheets("Error").Select
    SolverReset
    
    SolverAdd CellRef:="$I:$K" & i, Relation:=1, FormulaText:="1"
    SolverAdd CellRef:="$I:$K" & i, Relation:=3, FormulaText:="0"
    SolverOk SetCell:="$M" & i, MaxMinVal:=1, ValueOf:=0, ByChange:="$I:K" & i, _
        Engine:=3, EngineDesc:="Evolutionary"
    SolverOk SetCell:="$M" & i, MaxMinVal:=1, ValueOf:=0, ByChange:="$I:$K" & i, _
        Engine:=3, EngineDesc:="Evolutionary"
    SolverSolve UserFinish = True
    Next i
    
End Sub

Excel Format

I think you need to fix the CellRefs, you have "$I:$K" & i but i think it should be "$I" & i & :$K" & i same for the ByChange. — majjam, Feb 25 '21 at 22:43
I think that is the issue, but I can't find the right syntax. In yours, it doesn't seem to like the &: so I tried to separate it out into this ByChange:="$I" & i & "$J" & i & "$K" & i. No syntax error, but the same Error in Model occurs — Adam, Feb 25 '21 at 23:11

score 0 · Answer 1 · answered Feb 27 '21 at 04:21

Something like this, perhaps.

Sub Macro1()
    
Dim i As Integer
For i = 1 To 10

    SolverOk SetCell:="$A$1", MaxMinVal:=3, ValueOf:=60, ByChange:="$A$" & i & ":$D$" & i, _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$A$1", MaxMinVal:=3, ValueOf:=60, ByChange:="$A$" & i & ":$D$" & i, _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve

Next i

End Sub

Don't forget to set a reference to Solver.

In VBA editor...Go to Tools --> References --> and select Solver. Click OK.

How to automate solver in VBA

1 Answers1