SolverAdd

[Guest]

I am trying to use the Solver function and have trouble to get the
constraints set up properly. Of the 4 constraints only 2 are used as I can
tell by the result as well as by looking at the Solver Parameter window in
which only constraints 2 and 4 are listed. Changing for example the sequence
didn't help.

Version: Excel 2002 SP3, VB 6.3

- Bernd


Sub Macro1()

' Reset - Clear all previous settings
SolverReset

' Precision 0.1%; Use quadratic extrapolation
SolverOptions precision:=0.001, estimates:=1

' Minimize value for 1st dimentsion
SolverOk SetCell:=Range("$M$21"), _
MaxMinVal:=2, _
ByChange:=Range("$B$21:$k$21")

' Constraint 1 - Upper limit for weights
SolverAdd cellRef:=Range("$B$21:$k$21"), _
relation:=1, _
formulaText:=1

' Constraint 2 - Lower limit for weights
SolverAdd cellRef:=Range("$B$21:$k$21"), _
relation:=3, _
formulaText:=0

' Constraint 3 - Sum of all weights equal 100%
SolverAdd cellRef:=Range("$a$21"), _
relation:=2, _
formulaText:=1

' Constraint 4 - Target value for 2nd dimension
SolverAdd cellRef:=Range("$L$21"), _
relation:=2, _
formulaText:=Range("$o$21")

SolverSolve Userfinish:=True

End Sub

 

[Dana DeLouis]

I don't have an answer, as mine loaded just fine. I would be interested to
learn if the following worked for you...

'//...code...etc

'// Do these last...
SOLVEROPTIONS Precision:=0.001
SOLVEROPTIONS Estimates:=1

SolverSolve Userfinish:=True


HTH

 

[Guest]

Dana,

Your suggestion didn't make a difference. Nevertheless, your remark that it
worked fine when you run it baffled me. Thus, I took the code and copied it
into a new workbook/module. Just as in your case, it worked flawlessly
loading all the constraints. I am assuming now, that there is a problem with
the spreadsheet itself. I will rebuild it and try again.

Thanks a lot for helping me narrowing it down.

- Bernd

 

[halfsoul]

'See this thread.' (http://excelforum.com/showthread.php?t=492647)