Excel Solver

[HProutt]

I am having trouble writing code to use solver to get the correct asset allocation percentages. The problem I am having occurs when I enter my constraints. It seems that excel is only picking up 2 of my three constraints. Specifically, it is not picking up my constraint that says that the total of all assets must equal 1 (100%).


'Solve 2 - 10 Barbell

'Clears all solver constraints
SolverReset
'Sets the target cell equal to the the 5yr duration
SolverOk SetCell:="$K$44", MaxMinVal:=3, ValueOf:=Range("$K$41").Value, ByChange:= _
"$F$42,$I$42"
'Adds constraints that sum of 2 weightings must = 1 and percentages must be (>=) greater than or equal to 0.
SolverAdd CellRef:="$k$42", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$f$42", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$i$42", Relation:=3, FormulaText:="0"
'Tells the computer to solve with the information given.
SolverSolve UserFinish:=True
'Accepts the computer's solution without displaying the dialogue box
SolverFinish KeepFinal:=1


When I look in excel after I run the macro I only see the constraints that the weightings (F42, I42) must be >=0, I do not see that the sum must be equal to 1 (100%). Because I am not running the SolverReset function between when the macro completes and when I look at Solver interactively in excel I think that my constraints should still be there.

Please let me know if theres any flaw to my logic or my code. Thanks!

 

[SkipVought]

Have you tried doing what you want to do directly on your spreadsheet in Excel, using the Solver function? Does it work there?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HProutt]

Yeah it works fine. I believe my code is not picking up all my constraints. Interactively it can definitely pick up my constraints.

 

[combo]

In my case your code adds three constrains to fresh worksheet.
BTW, if K42 is the sum of F42 and I42, you can work with one variable (say F42), formula =1-F42 in I42 and two constrains: F42>=0 and F42<=1.

combo

 

[HProutt]

Could it have something to do with my version of excel (2002)?

 

[combo]

Tested with 2003.
Yoy can try to unhide solver names. Run the code below having sheet with solver active:

For Each n In ActiveSheet.Names
  n.Visible = True
Next n

Next check names, constrain x is coded with solver_lhs[!]x[/!], solver_rel[!]x[/!] and solver_rhs[!]x[/!].

combo