Home:ALL Converter>VBA+Solver loop not printing results for each row

VBA+Solver loop not printing results for each row

Ask Time:2012-09-21T23:32:43         Author:Roi Danton

Json Formatter

I am a Visual Basic for Application beginner and got the following problem.

I have to use the Excel Solver to change two variable cells in one row, to let go another cell to zero in the same row. The cells are connected by simple calculations in Excel itself. Also some constraints have to be defined.

Using the code below, only the last defined row will be rewritten into the cells in excel. The rows before were calculated but not rewritten.

Sub solverloop() 

    Dim i As Long 
    For i = 96 To 154 
        SolverReset 
        SolverOk SetCell:="$AE$" & i, MaxMinVal:=3, ValueOf:=0, ByChange:=Range(Cells(i, "V"), Cells(i, "W")), Engine:=1, EngineDesc:="GRG-Nichtlinear" 

        SolverAdd CellRef:=Range(Cells(i, "V"), Cells(i, "W")), Relation:=3, FormulaText:="0" 
        'Solving the Modell, but message box which must be confirmed by the user will not be displayed
        SolverSolve UserFinish:=True
        'Finishing the model and keep the last result
        SolverFinish KeepFinal:=1 
    Next i

End Sub 

Author:Roi Danton,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/12533631/vbasolver-loop-not-printing-results-for-each-row
yy