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!

 

[Andrzejek]

This is a group for Visual Basic(Microsoft): Version 5 & 6 Forum

Since this is VB[red]A[/red] - you should post your question in
VBA Visual Basic for Applications (Microsoft) Forum

It is amazing to me how many people don't even know which language they program in.... Unless it is an honest mistake.

Have fun.

---- Andy

 

[guitarzan]

Andrzejek:

Microsoft didnt do anyone any favors with the way they named these products. Do a Help > About in the macro editor, and it shows: Microsoft Visual Basic 6.3. Not "VBA 6.3", but "Visual Basic 6.3" !!! Yikes.