How to automate solver in VBA?

Question

I want to automate the excel solver with variable ranges and data. Formulated the following code, but not able to solve it

Sub excel_solver()
'
' excel_solver Macro
'

'
    SolverReset
    SolverOk SetCell:=ActiveCell, MaxMinVal:=2, ValueOf:=0, ByChange:=ActiveCell.Offset(0, -19).Range(Selection, Selection.End(xlDown)).Select, Engine:=2, EngineDesc:="Simplex LP"
    SolverAdd CellRef:=ActiveCell.Offset(0, -19).Range(Selection, Selection.End(xlDown)).Select, Relation:=5, FormulaText:="binary"
    SolverAdd CellRef:=ActiveCell.Offset(0, 2).Range(Selection, Selection.End(xlDown)).Select, Relation:=2, FormulaText:="1"
    SolverOk SetCell:=ActiveCell, MaxMinVal:=2, ValueOf:=0, ByChange:=ActiveCell.Offset(0, -19).Range(Selection, Selection.End(xlDown)).Select, Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$AC$29542", MaxMinVal:=2, ValueOf:=0, ByChange:=ActiveCell.Offset(0, -19).Range(Selection, Selection.End(xlDown)).Select, Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve

End Sub

[How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, May 17 '19 at 06:22

score 0 · Answer 1 · answered May 17 '19 at 06:26

This worked-
SolverReset
    SolverOk SetCell:=ActiveCell, MaxMinVal:=2, ValueOf:=0, ByChange:= _
        Range(ActiveCell.Offset(0, -19), ActiveCell.Offset(0, -19).End(xlDown)), Engine:=2, EngineDesc:="Simplex LP"
    SolverAdd CellRef:=Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 2).End(xlDown)), Relation:=2, FormulaText:="1"
    SolverAdd CellRef:=Range(ActiveCell.Offset(0, -19), ActiveCell.Offset(0, -19).End(xlDown)), Relation:=5, FormulaText:="binary"
    SolverOk SetCell:=ActiveCell, MaxMinVal:=2, ValueOf:=0, ByChange:= _
        Range(ActiveCell.Offset(0, -19), ActiveCell.Offset(0, -19).End(xlDown)), Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:=ActiveCell, MaxMinVal:=2, ValueOf:=0, ByChange:= _
        Range(ActiveCell.Offset(0, -19), ActiveCell.Offset(0, -19).End(xlDown)), Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve

How to automate solver in VBA?

1 Answers1