Solver cannot loop over rows with linked formulas?

Question

I have a spreadsheet that contains multiple columns/rows. I am trying to apply Solver for certain rows in a column.

I have my formulas set in column 22, I have defined initial values that needs to be solved by Solver in column 24, and I want to match the value in the column 10 for each row.

So far, I have developed this code:

Sub SolverLoop()
Dim i As Long

For i = 4 To 10

    SolverReset
    SolverOk SetCell:=Cells(i, 22).Address, MaxMinVal:=3, ValueOf:=Cells(i, 10).Address, ByChange:=Cells(i, 24).Address, Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve Userfinish:=True

Next

End Sub

But this is not doing anything to the column 24, which I expect to change. What am I missing?

Formula in the column 22 is =R123C12*NORMSDIST(RC[-2])-RC[-10]*EXP(-R16C2)*NORMSDIST(RC[-1]). Subsequently, in 20 and 21 are =(LN(R123C12/RC[-8])+(R15C2+RC[4]^2/2)*R14C2)/(RC[4]*SQRT(R14C2)) and =RC[-1]-RC[3]*SQRT(R14C2), respectively.

still no change in the column 24 :( and I cannot match the value in column 10. — AK88
What's your formula in column 22? Would help get your question to a minimal reproducible example — BigBen

AK88 AK88 · Accepted Answer · 2020-02-19T21:08:48

Sub SolverLoop()
Dim i As Long

For i = 4 To 10

    SolverReset
    SolverOk SetCell:=Cells(i, 22), MaxMinVal:=3, ValueOf:=Cells(i, 10).Value, ByChange:=Cells(i, 25), Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve Userfinish:=True

Next

End Sub

Looks like I was just missing .Value part for ValueOf:=Cells(i, 10).Value.

Solver cannot loop over rows with linked formulas?

1 Answers