VBA+Solver loop not printing results for each row
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
The SolverReset should clean the information inside the solver, for example the target cell.
SolverOK is the command to define the solver operation. The command works like a remote controller. It can be done with a mouse or keyboard. The defined cells are the same as you can see when you open the solver window by mouse.
SolverAdd, added the cells which can be changed by the software(solver itself) to achieve the defined value in the SolverOK function.
SolverSolve is the same as you press the button, which starts the Solver solving the defined informations. with the add UserFinish:=True the button must not pressed every row.
SolverFinish should keep the achieved solution and "should" write it back into the excel cell.