Solver and AutoFilter in Macro Excel 2010

[blucloudysky13]

Hi guys,

I am having a lot of difficulty trying to figure out how the macros work. I've looked at several forums and guides for several hours now trying to understand it, but I can't.

I am doing a financial model. I have a list of bonds with maturity dates and an input for the date of today. If the date of today is over the expiration date, the value = NA. So, I am trying to filter out values in a list that are =NA and then using Solver to find the weights I need on the list of bonds that have values. But, then when I change the date to something higher, the Solver messes up and it starts to put in values in another column or above/below where it should be. Please help me! I am a newbie when it comes to macros, but I really would like to understand how it's supposed to work. Here is what's on the VBA:

Sheets("Bonds").Select
ActiveSheet.AutoFilter.ApplyFilter
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveCell.Offset(-25, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "0%"
ActiveCell.Offset(1, 0).Range("A1").Select
SolverReset
SolverOk SetCell:="$X$43", MaxMinVal:=1, ValueOf:=0, ByChange:="$V$16:$V$41", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$V$16:$V$41", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$V$43", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$W$43", Relation:=2, FormulaText:="$R$43"
SolverOk SetCell:="$X$43", MaxMinVal:=1, ValueOf:=0, ByChange:="$V$16:$V$41", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$X$43", MaxMinVal:=1, ValueOf:=0, ByChange:="$V$16:$V$41", _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
Sheets("Planned Budget Balance Sheet").Select
ActiveCell.Offset(-1, 0).Range("A1").Select

Let me know if my explanation of what I'm trying to do is unclear. Thank you!

 

[blucloudysky13]

If there is anther way to do it, please let me know. I'm trying to get solver to change its 'by changing cells' area to only select the cells that do not have NA. I have screenshots below for two different scenarios. The first is when the date is set so that all the bonds work, but the second scenario is when the date is set so that some of the bonds are expired.

Here's the first scenario (ignore those highlighted cells). All the bonds are listed because none expire, so I want the Weights of all these bonds.

-- removed inline image ---

Please let me know if something can be done. Anything at all. Thank you!

 

[shg]

I think the code you have could be simplified to this:

    Worksheets("Bonds").Select
    Range("Weights").Value = 0
    SolverReset
 
    SolverOk SetCell:="X43", _
             MaxMinVal:=1, _
             ByChange:="V16:V41", _
             Engine:=1, _
             EngineDesc:="GRG Nonlinear"
 
    SolverAdd CellRef:="Weights", _
              Relation:=3, _
              FormulaText:="0"
    SolverAdd CellRef:="V43", _
              Relation:=2, _
              FormulaText:="1"
    SolverAdd CellRef:="W43", _
              Relation:=2, _
              FormulaText:="R43"
    SolverSolve
    Application.Goto Worksheets("Planned Budget Balance Sheet").Range("A1")

... where Weights is a (dynamic) named range that refers to the weighting values for the bonds that are not #N/A. (You can create a static range for testing.)

I'm skeptical of the last constraint. Constraints in Solver should be constants, i..e., not change in response to the changing cells. See http://www.solver.com/suppstdconstraint.htm

I think this might be hard to solve via remote control. If you put a workbook on box.net and post a link, I'll look at it.