how to run solver continuously for several rows

[hirastikanah]

hi
i want to plot an efficient frontier in excel
once i saw this video VBA Excel - Optimization Efficient Frontier - YouTube
i wondered how he plots it by clicking on optimize button while i have to do it row by row !

how should i follow this method?!

thank you

 

[GooberTron]

The Stock/Return values are different between the workbooks (cell C5). C = 14% in one, C = 17% in the other (I didn't ever modify these values).

 

[hirastikanah]

yeaaaaa

thank you sooooooooooooooooooooooooo much
I wish I could repay your kindness

thanks a lottttt

 

[GooberTron]

No problem - at least we got there in the end!

The final code for anyone in the future was;

Option Explicit

'''Separate function to find the last used row in a defined column letter
Private Function FindLastRow(ColumnToCheck As String) As Long

    FindLastRow = Range(ColumnToCheck & Rows.Count).End(xlUp).Row

End Function


Sub Optimise()

    'The starting row - this would be better done automatically in code but I don't know your sheet layout
    Const lSTARTROW As Long = 10
    Dim lEndRow As Long
    Dim i As Long
    Const GUESS_E As String = "50%", GUESS_F As String = "30%", GUESS_G As String = "20%"
    
    Application.ScreenUpdating = False
    
    lEndRow = FindLastRow("D")
    
    For i = lSTARTROW To lEndRow
        'set initial values programmatically
        ActiveSheet.Cells(i, 5).FormulaR1C1 = GUESS_E
        ActiveSheet.Cells(i, 6).FormulaR1C1 = GUESS_F
        ActiveSheet.Cells(i, 7).FormulaR1C1 = GUESS_G
        'Solver section
        SolverReset
        'Constraints
        SolverAdd CellRef:=Cells(i, 3), Relation:=2, FormulaText:=Cells(i, 2)
        SolverAdd CellRef:=Cells(i, 5), Relation:=3, FormulaText:="0"
        SolverAdd CellRef:=Cells(i, 6), Relation:=3, FormulaText:="0"
        SolverAdd CellRef:=Cells(i, 7), Relation:=3, FormulaText:="0"
        SolverAdd CellRef:=Cells(i, 8), Relation:=2, FormulaText:="1"
        'Run Solver to minimise D by chaging E,F,G columns
        SolverOk SetCell:=Cells(i, 4), MaxMinVal:=2, ValueOf:=0, ByChange:=Range(Cells(i, 5), Cells(i, 7)), Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True
    Next i

    Application.ScreenUpdating = True
    
End Sub

 

[hirastikanah]

thanks again

 

[chinnybeer]

Hi there, I have been trying something very similar but it still doesn't work.

I am simply changing one cell (RK)to give a result in another cell (RP) then loop through all the rows in the data to the next row of RK & RK

Code below:

Sub solve()


Dim i As Integer
Sheets("raw_data").Activate


Set A = AddIns("Solver Add-In")
If A.Installed = True Then

Else
MsgBox "The Solver add-in is not installed"
End If
numrows = Range("RP5", Range("RP5").End(xlDown)).Rows.Count

Range("RP5").Select

For i = 1 To numrows
SolverReset
SolverOk SetCell:=Cells(i, 484), MaxMinVal:=3, ValueOf:=0, ByChange:=Cells(i, 485), Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve UserFinish:=True




Application.Calculation = xlAutomatic




Next i




End Sub

Any help would be gratefully recieved