VBA code for loops and using Solver over multiple iterations

Amanda_Jones81

New Member
Joined
Nov 11, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hi,
I have a basic network flow problem (maximizing flow from source to sink). I would like to "attack" an arc, use the solver to find the optimal solution, paste that solution in a different cell, and then loop thru to iterate to the next row and do the same thing until I get thru all of my arcs.

To be a bit more specific:
I have a column M17-M29 that are the upper bound constaints for each arc
Column N17-N29 are the arc capacities- these are formulated tochange to 0 if the corresponding row in column O is changed from 0 to 1
Column O17-O29- these are all 0 to begin with. I want to loop thru and change the first row to 1 and keep the remaining as 0 and then after the solver, change the 1 back to 0 and make the next row in column O (i.e. O18) a 1 and the rest 0...so on so forth all the way thru O29.
Column Q17-Q29- these are the values that are changed within the solver and have constraints applied to them. Q29 is also my objective function.

First loop thru the solver determines value in Q29, I want to copy all of Q17-Q29 to a new column and then reset the solver and do another iteration started with the next row like I mention above then solve again and able to copy all of the new values from Q17-Q29 into a different column so on so forth. till I iterate thru all of the arcs.

Solver constraints look like this when I record a macro to see what the solver code looks like: J and L referenced below are LHS and RHS constraints set in a different location on the worksheet

Solver Reset
SolverOk SetCell:="$Q$29", MaxMinVal:=1, ValueOf:=0, ByChange:="$Q$17:$Q$29", _
Engine:=2, EngineDesc:="Simplex LP"
SolverAdd CellRef:="$Q$17:$Q$29", Relation:=1, FormulaText:="$N$17:$N$29"
SolverAdd CellRef:="$J$7:$J$13", Relation:=2, FormulaText:="$L$7:$L$13"
SolverOk SetCell:="$Q$29", MaxMinVal:=1, ValueOf:=0, ByChange:="$Q$17:$Q$29", _
Engine:=2, EngineDesc:="Simplex LP"

SolverSolve

Any help would be greatly appreciated!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top