Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using Solver with VBA
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 |
#2
|
|||
|
|||
I would check two things.
1. Use the cell addresses for SetCell and for ByChange, not range objects. 2. Do SolverOK just before SolverSolve - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ EggShell wrote: 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 -------------------- |
#3
|
|||
|
|||
Hi. Don't have an answer, but I would be curious for any feedback if you
deleted the following line for debugging ... Call SolverOptions(MaxTime:=100... ' Add the constraint that Final Inventory = Capacity SolverAdd CellRef:=Range("Final_Inventory").Offset(0, intOffset), Relation:=1, .... Note that if you really want "Equal", I believe that Relation should be 2, and not 1. Also, make sure your sheet is using A1 reference, and not R1C1 reference for debugging. -- Dana DeLouis Win XP & Office 2003 "EggShell" wrote in message ... 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 -------------------- -- EggShell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using solver with function with multiple outputs | Excel Worksheet Functions | |||
Solver and dynamic ranges | Excel Worksheet Functions | |||
Solver Problems | Excel Worksheet Functions | |||
Excel: Solver | Excel Worksheet Functions | |||
solver constraint | Excel Worksheet Functions |