Need Macro to repeat Solver for several scenarios

EDIT: Solver criteria obviously changes with each column. I realized that I used criteria for column D when I notated "Starting in column C" - it should have said "starting with column D" Thanks!

Try this macro ans see if it works the way you wish.

Option Explicit

Sub Solver_loop()
Dim i As Long

For i = 3 To 101

SolverReset
    SolverOk SetCell:=Cells(22, i).Address, MaxMinVal:=2, ValueOf:="0", ByChange:=Range(Cells(17, i), Cells(19, i)).Address
    SolverAdd CellRef:=Range(Cells(17, i), Cells(19, i)).Address, Relation:=3, FormulaText:="0"
    SolverAdd CellRef:=Cells(20, i).Address, Relation:=2, FormulaText:="1"
    SolverAdd CellRef:=Cells(22, i).Address, Relation:=2, FormulaText:=Cells(21, i).Address
    SolverSolve Userfinish:=True
Next i
End Sub

Alf

Originally Posted by Alf

Try this macro ans see if it works the way you wish.

Option Explicit

Sub Solver_loop()
Dim i As Long

For i = 3 To 101

SolverReset
    SolverOk SetCell:=Cells(22, i).Address, MaxMinVal:=2, ValueOf:="0", ByChange:=Range(Cells(17, i), Cells(19, i)).Address
    SolverAdd CellRef:=Range(Cells(17, i), Cells(19, i)).Address, Relation:=3, FormulaText:="0"
    SolverAdd CellRef:=Cells(20, i).Address, Relation:=2, FormulaText:="1"
    SolverAdd CellRef:=Cells(22, i).Address, Relation:=2, FormulaText:=Cells(21, i).Address
    SolverSolve Userfinish:=True
Next i
End Sub

Alf

Alf - this is fantastic! Thank you so much. The only issue (which I may be able to solve on my own) is that of of the constraints is that row 20 must sum to 100% for each column (no leverage). Any thoughts? Thank you again!

Originally Posted by Alf

Try this macro ans see if it works the way you wish.

Option Explicit

Sub Solver_loop()
Dim i As Long

For i = 3 To 101

SolverReset
    SolverOk SetCell:=Cells(22, i).Address, MaxMinVal:=2, ValueOf:="0", ByChange:=Range(Cells(17, i), Cells(19, i)).Address
    SolverAdd CellRef:=Range(Cells(17, i), Cells(19, i)).Address, Relation:=3, FormulaText:="0"
    SolverAdd CellRef:=Cells(20, i).Address, Relation:=2, FormulaText:="1"
    SolverAdd CellRef:=Cells(22, i).Address, Relation:=2, FormulaText:=Cells(21, i).Address
    SolverSolve Userfinish:=True
Next i
End Sub

Alf

Fixed. I just changed:

SolverAdd CellRef:=Cells(20, i).Address, Relation:=2, FormulaText:="1"

to

SolverAdd CellRef:=Cells(20, i).Address, Relation:=2, FormulaText:="100%"

Thanks again for your help! Very much appreciated!

QUOTE] is that of the constraints is that row 20 must sum to 100%[/QUOTE]

Strange as the solver command is set so that the value of row 20 should be 1 (format must of course be set as %)

SolverAdd CellRef:=Cells(20, i).Address, Relation:=2, FormulaText:="1"

Where "Cells(20, i)" is row 20 and i is column number going from 3 (C column) to 101 (CW column)

Will have a look at it to morrow.

Alf

I agree that is odd. "100%" or "1" should yield the same result. Thanks again.

Glad you fixed it. Since this seems to solve your problem could you please mark the thread "Solved" and if you like click on the small star in my post (bottom lift) and give a rating to my "efforts"

Alf

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Thanks for rep and feed back. Analyzing the result a see a problem starting with portfolio 23. As soon as there is only one weight (row 17 to 19) the constraint that
row 22 = row 21 don't "work" any more.

Perhaps this needs an extra constraint that ensures that at least 2 weights must be chosen? Or do you have any explanation?

Almost midnight here in Sweden so I'm off to bed!

Alf

That's funny you mention that. I actually just made an edit to my master version just before reading your comment. The example I gave you contained several portfolios that were not theoretically possible due to the asset characteristics. I've since fixed it in the master so the issue no longers exists. Because this is an efficient frontier, the 1st asset (Row 17) was never actually used due to its relative inefficiency. I'm thinking I'll change the constraint to ensure at least two (or 3) weights are chosen for some different scenario analyses I'll be doing. End result should be a portfio of the "feasible set" in addition to the efficient frontier.