HELP! Solver VBA variable range loop

[jimmyvba]

Hi Guys,

Would be really greatful for a solution. I am trying to write a VBA loop for solver function where the Target and constraints will depend on the futures contract chosen in a cell but am just not being able to make it work. Here is the macro, that i came up with. Here, i have kept the futures contract fixed as ERM4 but in reality it would be linked to a cell. Also, i am aware the solver works only for active sheet if i am not wrong. I am fine even if it works on the same sheet as the target and constraints but ideally i would like it to work on a different sheet. Here is the code. Any help would be greatly appreciated. Thanks in advance.

Sub Solv()
Dim sigma_rng As Range
Dim alpha_rng As Range
Dim beta_rng As Range
Dim rho_rng As Range
Dim target_rng As Range
Dim rng As Range
Dim i As Integer
Dim i_row As Integer
Dim sheet_name As String
futures_name = "ERM4"
i = 9
Do While Worksheets("Euribor").Cells(i, 1).Value > 0
If Worksheets("Euribor").Cells(i, 4).Value = futures_name Then
i_row = i
End If
i = i + 1
Loop

Set sigma_rng = Worksheets("Euribor").Cell(i_row, 19)

Set alpha_rng = Worksheets("Euribor").Cells(i_row, 20)
Set beta_rng = Worksheets("Euribor").Cells(i_row, 21)
Set rho_rng = Worksheets("Euribor").Cells(i_row, 22)
Set target_rng = Worksheets("Euribor").Cells(i_row, 24)
Set rng = Range(Worksheets("Euribor").Cells(i_row, 19), Worksheets("Euribor").Cells(i_row, 22))

'SolverReset
SolverOk SetCell:=target_rng, MaxMinVal:=2, ByChange:=rng, _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:=alpha_rng, Relation:=3, FormulaText:="0"
SolverAdd CellRef:=beta_rng, Relation:=3, FormulaText:="0"
SolverAdd CellRef:=beta_rng, Relation:=1, FormulaText:="1"
SolverAdd CellRef:=rho_rng, Relation:=3, FormulaText:="-0.9999"
SolverAdd CellRef:=rho_rng, Relation:=1, FormulaText:="0.9999"
SolverSolve UserFinish:=False

End Sub

 

[jimmyvba]

code sorted!

 

[machib77]

code sorted!

Can you show me how?