Amanda_Jones81
New Member
- Joined
- Nov 11, 2020
- Messages
- 3
- Office Version
- 2010
- Platform
- 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!
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!