For Next Loop with Solver - How to reference each count in the range?

[Jardman123]

Hi guys I am creating a model for a uni assignment. Basically this macro needs to create 50 portfolios of assets and copy and paste their information to my table. All I want to do is use a constraint as the 'min' that starts in H2 and ends in BE2, this constraint is used against the portfolio returns box I have set up, it pumps out the numbers and it then needs to copy and paste this numbers to the respective columns for each portfolio (1-50).

All I need help with is creating this loop and referencing the range used for the constraint and copy and paste values against the count period.

Here is my code;

Sub EfficientFrontier()
'
' EfficientFrontier Macro
' Calculates the efficient frontier from the given data set and minimum variance & optimal portfolios.
'
' Keyboard Shortcut: Ctrl+k
'
For Count = 8 To 58

Set curCell = Worksheets("Data").Cells(2, Counter)

SolverOk SetCell:="$C$27", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$20:$E$20"
SolverAdd CellRef:="$F$20", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$C$27", Relation:=2, FormulaText:="Data.Cells(2, Counter)"
SolverOk SetCell:="$C$27", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$20:$E$20"
SolverSolve True

Range("$C$28:$C$29").Select
Selection.Copy
Range("Data.Cells(3, Counter):Data.Cells(4, Counter)").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("$A$22:$A$24").Select
Application.CutCopyMode = False
Selection.Copy
Range("Data.Cells(5, Counter):Data.Cells(7, Counter)").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next Count
End Sub

Thanks for your time!

[Jardman123]

Bump, please help guys

[ragulduy]

I could be way off but try replacing "counter" with "count"?

[Jardman123]

Yeah Ive fixed that up, I still get a message saying global range error or something

[ragulduy]

Oh right, on this line:

Please Login or Register  to view this content.

You probably want something like:

Please Login or Register  to view this content.

and the same for:

Please Login or Register  to view this content.

[Alf]

Do you have all the data that solver should work with on the same sheet? According to the solver home page:

By Changing Cells must be on the active sheet.

A limitation of the Solver is that all of the decision variables (adjustable or changing cells) in the By Changing Cells edit box must be cells on the active sheet. (This limitation makes the Solver considerably faster than if adjustable cells were allowed to be on any sheet.) You should re-design your Solver model so that all decision variables are on one sheet, and try again.

Alf

[Jardman123]

It's all on the same sheet, yeah.

Yudlugar you did it man, thanks a lot...that's not to say the model works, but it is running it 50 times and pasting it where it should go.

Now the values are out of whack, solver isn't paying any attention to the constraints I have set...

[Jardman123]

It is not running solver at all anymore..it copy and pastes the values 50 times though... heres the new code;

Sub efficientfrontier()
'
' efficientfrontier Macro
'
' Keyboard Shortcut: Ctrl+k
'
For counter = 8 To 57
Set curCell = Cells(2, counter)
SolverReset
SolverOk SetCell:="$C$27", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$20:$E$20"
SolverAdd CellRef:="$C$27", Relation:=2, FormulaText:="Cells(2, Counter)"
SolverAdd CellRef:="$F$20", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$C$20", Relation:=3, FormulaText:="-50"
SolverAdd CellRef:="$D$20", Relation:=3, FormulaText:="-50"
SolverAdd CellRef:="$E$20", Relation:=3, FormulaText:="-50"
SolverSolve True
Application.CutCopyMode = False
Range("C28:C29").Select
Selection.Copy
Range(Sheets("Data").Cells(3, counter).Address & " : " & Sheets("Data").Cells(4, counter).Address).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A22:A24").Select
Application.CutCopyMode = False
Selection.Copy
Range(Sheets("Data").Cells(5, counter).Address & " : " & Sheets("Data").Cells(7, counter).Address).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Next counter
End Sub

[Jardman123]

Now my other macro won't work.... how come it always stuffs up even when I change nothing??

This is the code for finding a minimum variance portfolio and the optimal portfolio...

It's not running the minimum solver, just the maximum (optimal), then copy pasting the values of the optimal portfolio to both locations.

Sub OptimalMinimum()
'
' OptimalMinimum Macro
' Creates the minimum variance and optimal portfolio.
'
' Keyboard Shortcut: Ctrl+l
'
SolverReset
SolverOk SetCell:="$C$28", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$20:$E$20"
SolverAdd CellRef:="$F$20", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$C$20", Relation:=3, FormulaText:="-50"
SolverAdd CellRef:="$D$20", Relation:=3, FormulaText:="-50"
SolverAdd CellRef:="$E$20", Relation:=3, FormulaText:="-50"
SolverSolve True
Range("C27:C29").Select
Selection.Copy
Range("I10:I12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C20:E20").Select
Application.CutCopyMode = False
Selection.Copy
Range("I13:I15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
SolverReset
SolverOk SetCell:="$C$29", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$20:$E$20"
SolverAdd CellRef:="$F$20", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$C$20", Relation:=3, FormulaText:="-50"
SolverAdd CellRef:="$D$20", Relation:=3, FormulaText:="-50"
SolverAdd CellRef:="$E$20", Relation:=3, FormulaText:="-50"
SolverSolve True
Range("C27:C29").Select
Selection.Copy
Range("J10:J12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C20:E20").Select
Application.CutCopyMode = False
Selection.Copy
Range("J13:J15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
End Sub

[Jardman123]

I fixed the issue just posted ^ by making a reset code for the weights of the portfolio...can someone tell me how to properly reference this constraint cell for the count..solver isnt running against my constraints.

In short; how do I reference this properly - SolverAdd CellRef:="$C$27", Relation:=2, FormulaText:="Cells(2, Counter)"

[Jardman123]

SolverAdd CellRef:="$C$27", Relation:=2, FormulaText:="Cells(2, Counter)" is referencing a value in that cell, so do I need to stipulate this in the code?

[Jardman123]

So basically my question is how to reference the cell properly into the solver constraint - please help!!

[Jardman123]

Bump...doesn't seem like a rare/hard problem to those with knowledge

[ragulduy]

I broke my solver reference somehow so I can't check it but I'd imagine you want :

Please Login or Register  to view this content.

[shg]

Please Login or Register  to view this content.

[Jardman123]

Thanks Yud but I've tried that, and '.select' and '.value' to no avail. I'm beginning to think Solver is too clunky to even reference a cell for constraint or 'value of'

[Jardman123]

SHG you legend!!!!!!! you cracked the da vinci code...simply by removing the apostrophes

[shg]

If that cell is changing during calculation, it won't work; Solver's constraints are static, so the value is all you need.