SolverAdd

[Bernd]

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
--
Dana DeLouis
Win XP & Office 2003


"Bernd" <[email protected]> wrote in message
news:[email protected]...
>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

[Bernd]

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


"Dana DeLouis" wrote:

> 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
> --
> Dana DeLouis
> Win XP & Office 2003

[halfsoul]

See this thread.