Excel Solver VBA

• Question

• I am a complete vba newbie. I managed to record a macro, I also managed to make it work.

Now I would like it to work for multiple rows. So \$BQ\$5 should be \$BQ\$6, \$BS\$5 should be \$BS\$6 and \$BA\$5 should be \$BA\$6 in the next loop. And all that for 682 rows. What must be changed in my formula?

Sub SolverIndia()

Dim i As Integer

For i = 0 To 6

SolverReset
'
SolverOk SetCell:="\$BQ\$5", MaxMinVal:=3, ValueOf:=Range("\$BS\$5").Value, ByChange:="\$BA\$5", _
Engine:=1, EngineDesc:="GRG Nonlinear"

SolverSolve (True)

SolverSolve userFinish:=True
SolverFinish KeepFinal:=1

Next i

End Sub

Saturday, November 30, 2019 4:38 PM

All replies

• Perhaps like this?

Sub SolverIndia()
Dim i As Long
For i = 5 To 686
SolverReset
SolverOk SetCell:="\$BQ\$" & i, MaxMinVal:=3, _
ValueOf:=Range("\$BS\$" & i).Value, ByChange:="\$BA\$" & i, _
Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve True
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
Next i
End Sub

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Saturday, November 30, 2019 5:44 PM
• thank you very much!!! You made my day and it works just perfect!
Saturday, November 30, 2019 5:48 PM