ExcelBanter - View Single Post

EggShell

I'm 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