none
Excel Solver VBA RRS feed

  • 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