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
--------------------