How to use solver multiple times for linear programming at once?

Question

I have the following problem!

I want to perform linear programming to maximize the Objective values (Y) with Solver for all rows 7 to 16 (total 10 times). However, I do not want use Solver 10 times. Is there any automated way to do this? For example, for the row 7, the problem can be formulated like this:

Max A7
subject to B7>=B2, C7>=B2, D7>=B2,
B7<=B3, C7<=B3, D7<=B3,
B7 + C7 + D7 = 100%

Likewise, for the row 8, the problem should be formulated like this: Max A8 subject to B8>=B2, C8>=B2, D8>=B2, B8<=B3, C8<=B3, D8<=B3, B8 + C8 + D8 = 100%

As you can see, every row has the same structure of formulation but different cells. Is there any way to achieve this without using Solver like 10 times? I would very very appreciate it!!!

Thank you very much!

You could script this in VBA with a loop. Is that what you are after or something else? — Alexis Olson
– Alexis Olson, Commented Feb 9, 2018 at 18:39
Thank you very much. Where can I possible find the information about VBA loop? — JungleDiff
– JungleDiff, Commented Feb 9, 2018 at 18:40
Have you considered making cell A5 = sum(A7:A16), then using cell A5 as the objective? But you may find that you hit a constraint limit in the solver... But don't Frontline do an "advanced solver" with more capacity? — Solar Mike
– Solar Mike, Commented Feb 9, 2018 at 20:31
Hi Solar, the sum of the objective functions is not what I want. I think you may have misunderstood. I need to iterate through for each of the cells. They are considered separate linear programming problems. — JungleDiff
– JungleDiff, Commented Feb 9, 2018 at 20:32

n.gause · Accepted Answer · 2018-02-09 19:39:47Z

1

Try something like this. Make sure you add the solver reference in VBA. It's still going to run solver each time but you don't have to keep clicking solver and changing the constraints every time. It will do it all in the background

Sub FindMaxYs()
Dim i As Integer
ActiveSheet.Cells(7, 1).Select
For i = 7 To 16
    ActiveSheet.Cells(i, 1).Select
    SolverReset
    SolverOk SetCell:=ActiveCell, MaxMinVal:=1, ValueOf:=0, ByChange:=Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)), _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:=ActiveCell.Offset(0, 1), Relation:=1, FormulaText:="$B$3"
    SolverAdd CellRef:=ActiveCell.Offset(0, 2), Relation:=1, FormulaText:="$B$3"
    SolverAdd CellRef:=ActiveCell.Offset(0, 3), Relation:=1, FormulaText:="$B$3"
    SolverAdd CellRef:=ActiveCell.Offset(0, 1), Relation:=3, FormulaText:="$B$2"
    SolverAdd CellRef:=ActiveCell.Offset(0, 2), Relation:=3, FormulaText:="$B$2"
    SolverAdd CellRef:=ActiveCell.Offset(0, 3), Relation:=3, FormulaText:="$B$2"
    SolverAdd CellRef:=ActiveCell.Offset(0, 4), Relation:=2, FormulaText:="$B$3"
    SolverSolve True
Next i
End Sub

answered Feb 9, 2018 at 19:39

n.gause

363 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

JungleDiff Over a year ago

Hi n.gause, first of all, thank you very much for the code! I am very new to VBA, and do not really know much syntax. May I just ask you what ActiveCell.Offset does?

n.gause Over a year ago

@JunJang It's just a way to reference a cell relative to an active cell. For example, say your active cell is B7. If you say ActiveCell.Offset(1, 2), your referring to the cell 1 row down and 2 columns to the right which would be C8.

JungleDiff Over a year ago

Got it. Thank you so much. May I ask you how you have the line: SolverAdd CellRef:=ActiveCell.Offset(0, 4), Relation:=2, FormulaText:="$B$3" ? I mean, this is exactly what I want (it is equivalent to the X1+X2+X3 = 100% constraint), but how does (0,4) come into play?

n.gause Over a year ago

@JunJang FYI you can also use it as a reference for non active cells. For example Range("B20").Offset(1, 1).Select would select cell C21.

n.gause Over a year ago

@JunJang I added a cell in column E for each row that would sum x1, x2, and x3. I then added a constraint to set that equal to 100.

|

Collectives™ on Stack Overflow

How to use solver multiple times for linear programming at once?

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related