I am using this code to run Solver with VBA. When I run it, there is no solution (or Solver Results dialog box). When I check the Solver under "Tools" and see that the constraints and MaxMinVal are set OK. But there are no entries for "Set Target Cell or "By Changing Cells". What have I done wrong?
Code:
Sub RunSolver()
'Prompt for month number
currMonth = Application.InputBox(Prompt:="Enter month number:", Type:=2)
'Clear previous Solver settings
SolverReset
' Solver Options...
Call SolverOptions(MaxTime:=100, Iterations:=200, Precision:=0.0001, AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True)
' The Solver will run one month at a time. Using the first month as
' the starting point, use intOffset to determine which month (column)
' to solve for
intOffset = currMonth - 1
' Set the target cell to a minimum value by changing cells C11:C22 or
' an offset of this range
SolverOk SetCell:=Range("Total_Cost"), MaxMinVal:=2, _
ByChange:=Range("Ship").Offset(0, intOffset)
' Add the constraint that Final Inventory = Capacity
SolverAdd CellRef:=Range("Final_Inventory").Offset(0, intOffset), Relation:=1, _
FormulaText:=Range("Capacity").Offset(0, intOffset)
' Add the constraint that Final Inventory = Safety Stock
SolverAdd CellRef:=Range("Final_Inventory").Offset(0, intOffset), Relation:=3, _
FormulaText:=Range("Safety_Stock").Offset(0, intOffset)
' Add the constraint that shipments to customer = customer demand.
SolverAdd CellRef:=Range("Net_Flow_Cust").Offset(0, intOffset), Relation:=2, _
FormulaText:=Range("Demand_Cust").Offset(0, intOffset)
' Add the constraint that shipments from PM = PM Production.
SolverAdd CellRef:=Range("Net_Flow_PM").Offset(0, intOffset), Relation:=2, _
FormulaText:=Range("Supply_PM").Offset(0, intOffset)
' Add the constraint that shipments from WHSE = WHSE Demand.
SolverAdd CellRef:=Range("Net_Flow_WHSE").Offset(0, intOffset), Relation:=3, _
FormulaText:=Range("Demand_WHSE").Offset(0, intOffset)
' Solve the model and keep the final results.
SolverSolve UserFinish:=False
'SolverFinish KeepFinal:=1
End Sub
Bookmarks