EXCEL Solver automation

Question

I'am using the Excel Solver to minimize a cost based on 4 variables.

The point is that solver can solve one target cell at a time but I need to do that on 250 items for multiple suppliers. I tried to sum up all the price but the solver is limited to 200 variables and it takes forever to perform it.

My question is: How can I automate the solver using a macro?

My data are organized as follows:

Prices: Column A to D Constraint on Qty: Column E Quantities: Column F to I Demand (depending on Quantities): Column J Total Price (to minimize): Column K

I tried get the following macro code for 1 item:

SolverOk SetCell:="$K$13", MaxMinVal:=2, ValueOf:=0, ByChange:="$F$13:$I$13", _
    Engine:=2, EngineDesc:="Simplex LP"

SolverAdd CellRef:="$F$13:$I$13", Relation:=4, FormulaText:="integer"
SolverOk

SolverAdd CellRef:="$F$13:$I$13", Relation:=3, FormulaText:="0"
SolverOk

SolverAdd CellRef:="$J$13", Relation:=3, FormulaText:="$E$13"
SolverOk

SolverSolve

SolverOk

SolverDelete CellRef:="$F$13:$I$13", Relation:=4
SolverDelete CellRef:="$F$13:$I$13", Relation:=3, FormulaText:="0"
SolverDelete CellRef:="$J$13", Relation:=3, FormulaText:="$E$13"

End Sub

I need your help to automate this code from line 7 to 257.

Thanks

David

Answer 1

I think the question you are really asking is how to create a LOOP in vba. for beginers, "for loops" are easiest. start your code with:

for i = 7 to 257

put your code here that you want to repeat... the thing you want to change, refer to "i"

This line for example would not be refering to the 13th row as it is here: SolverAdd CellRef:="$F$13:$I$13", Relation:=4, FormulaText:="integer"

but rather referring to the ith row as shown here

SolverAdd CellRef:="$F$" & i & ":$I$" & i, Relation:=4, FormulaText:="integer"

and so on. Do that throughout for the thing you want to change. and end your loop with this:

Next

let me know if this helped.

Answer 2

 For row = 1 To 10
    For col = 1 To 7
    SolverReset
    SolverOptions Precision:=0.001
    SolverOK SetCell:="INDEX(ObjFn," & row & "," & col & ")", _
    MaxMinVal:=3, _
    ValueOf:=0, _
    ByChange:="INDEX(ByChanging," & row & "," & col & ")"
    solverResult = Application.Run("SolverSolve", True)

    Next col
Next row

Here ObjFn is a named range on excel and ByChanging is another named range.