Excel Solver problem

[sudhakar682]

hi,

I wrote a small code to run solver. In the code I specified to add four constraints, but when I click the button (to run solver) only two constraints appear in the solver? Does anyone know why my other two constraints aren't appearing in the solver? Thanks very much for your time.

Public Sub Calculate_Equity_Duration_Module()

Dim ObjCell As Variant
Dim ChangeCell As Variant
Dim ConstraintOne As Variant
Dim ConstraintTwo As Variant
Dim ConstraintThree As Variant
Dim ConstraintFour As Variant
Dim Target_Return As Variant
Dim Plan_Duration As Variant


Worksheets("Equity_Duration").Activate
Target_Return = Range(Cells(2, 3), Cells(2, 3)).Value
Plan_Duration = Range(Cells(3, 3), Cells(3, 3)).Value

ObjCell = Cells(16, 3).Address()
ChangeCell = Cells(12, 3).Address() & ":" & Cells(14, 3).Address()
ConstraintOne = Cells(12, 3).Address() & ":" & Cells(14, 3).Address()
ConstraintTwo = Cells(12, 3).Address() & ":" & Cells(14, 3).Address()
ConstraintThree = Cells(15, 3).Address()
ConstraintFour = Cells(17, 3).Address()

SolverReset
SolverOk SetCell:=ObjCell, MaxMinVal:=3, ValueOf:=Target_Return, ByChange:=ChangeCell
SolverAdd CellRef:=ConstraintOne, Relation:=3, FormulaText:="1"
SolverAdd CellRef:=ConstraintTwo, Relation:=3, FormulaText:="0"
SolverAdd CellRef:=ConstraintThree, Relation:=2, FormulaText:="1"
SolverAdd CellRef:=ConstraintFour, Relation:=2, FormulaText:=Plan_Duration

SolverOptions MaxTime:=1000, Iterations:=10000, Precision:=0.0000000001, _
AssumeLinear:=False, StepThru:=False, Estimates:=1, Derivatives:=1, _
SearchOption:=1, IntTolerance:=0.000000000001, Scaling:=False, Convergence:= _
0.0000000001, AssumeNonNeg:=False

SolverSolve UserFinish:=True
SolverFinish keepFinal:=1

End Sub

 

[sudhakar682]

If anyone needs the spreadsheet, please send me your email address.

Thanks,

 

[Jon Peltier]

Try to rearrange the order of the Solver calls. This may not help you much, but Solver can be flaky, and in a recent project, after weeks of frustration, it worked for us. This is the order we settled on:

SolverOptions
SolverReset
SolverAdd (all of them here)
SolverOk
SolverSolve

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______