Need help with this review problem

[jgoods]

In this problem I have 3 plants shipping to 4 cities and I have to use solver in VBA to minimize the shipping costs for different scenarios (the scenarios are listed in the second worksheet), then print the results in the third worksheet. I'm confused on this part of our test. Thank you for any help.

13230

[mancubus]

please donot duplicate threads.

http://www.vbaexpress.com/forum/showthread.php?52351


The goal of this program is to minimize shipping cost for different scenarios which are listed in the second worksheet, then print the results in the third worksheet.
13215
I'm really confused by this section. This is just a practice problem for an upcoming test, but I have no idea how to go about it. I appreciate any help!

this kinda DIY thing. :) so start with a tutorial:
http://www.solver.com/excel-solver-help

googling "excel vba loops" will give you countless resources.

and i wish you good luck.

[James88]

Are you just using the normal solver tool or are you trying to use code?

[mancubus]

after mastering the tutorial, record a macro while manually using solver.
this will give you a start. modify it.

i will post my solution after this post. but you'd better build your own.


clear existing solver results, if any, in Results.
detect start and end columns for scenarios which will be used in the loop.
as constraints are the same first Reset Solver then add constraits. use SolverRest and SolverAdd functions.
assign starting row number (which is 3) to a long variable to paste in Results.

in loop:
first copy capacities and transpose of demands to model
use SolverOk function to define the model
use SolverSolve function to begind the solution. set UserFinish argument to True in order not to display the Solver Results Dialog Box. Otherwise the code will be interrrupted.
wait some time (3 to 5 seconds or more) for solver.
write results to Results.
increment paste row number by 6.

[mancubus]

Sub SoverLoopMacro()

Dim wsModel As Worksheet, wsScenarios As Worksheet, wsResults As Worksheet
Dim ResStartRow As Long, SceStartCol As Long, SceEndCol As Long, NumOfSce As Long
Dim j As Long, calc As Long

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
calc = .Calculation
.Calculation = xlCalculationAutomatic
End With

Set wsModel = Worksheets("Model")
Set wsScenarios = Worksheets("Scenarios")
Set wsResults = Worksheets("Results")

wsResults.UsedRange.Offset(1).Clear 'clear previous results, offset 1 row to keep header row.

With wsScenarios
SceStartCol = 2 'Column B
SceEndCol = .Cells(3, .Columns.Count).End(xlToLeft).Column 'scenario names are in row 3
NumOfSce = SceEndCol - SceStartCol + 1 'if needed
End With

'reset previous, add constraints to scenario
SolverReset
SolverAdd CellRef:="ShippedIn", Relation:=3, FormulaText:="Demands"
SolverAdd CellRef:="ShippedOut", Relation:=1, FormulaText:="Capacities"

ResStartRow = 3

For j = SceStartCol To SceEndCol
Range(wsScenarios.Cells(5, j), wsScenarios.Cells(7, j)).Copy 'copy capacities
wsModel.Range("I13").PasteSpecial Paste:=xlPasteValues
Range(wsScenarios.Cells(10, j), wsScenarios.Cells(13, j)).Copy 'copy demands
wsModel.Range("C18").PasteSpecial Paste:=xlPasteValues, Transpose:=True

SolverOk SetCell:="TotalCost", MaxMinVal:=2, ValueOf:=0, ByChange:="Shipped", _
Engine:=2, EngineDesc:="Simplex LP"
Solversolve (True) 'True for bypassing the dialogs
Application.Wait Now + TimeValue("00:00:03") 'wait 3 seconds for solver

With wsResults
.Cells(ResStartRow, 1).Value = "Scenario" & j - 1 '-1 because first iteration of j is 2
.Cells(ResStartRow + 1, 1).Value = "Shipments"
.Cells(ResStartRow + 1, 6).Value = "Total Cost"
Range("Shipped").Copy
.Cells(ResStartRow + 2, 1).PasteSpecial Paste:=xlPasteValues
Range("TotalCost").Copy
.Cells(ResStartRow + 2, 6).PasteSpecial Paste:=xlPasteValues
End With

ResStartRow = ResStartRow + 6 'increment 6 rows betw 2 scenarios
Next j

With Application
.CutCopyMode = False
.EnableEvents = True
.Calculation = calc
End With

End Sub