Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SolverAdd
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SolverAdd
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SolverAdd
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SolverAdd
'See this thread.' (http://excelforum.com/showthread.php?t=492647) -- halfsoul ------------------------------------------------------------------------ halfsoul's Profile: http://www.excelforum.com/member.php...o&userid=34063 View this thread: http://www.excelforum.com/showthread...hreadid=478618 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with SolverAdd. Can anyone help? | Excel Programming | |||
Problem implementing SolverAdd - Can anyone solve? | Excel Programming |