So I wrote a macro to run solver in a loop. Macro looks like this
Option Explicit
Sub SolverLoop()
Dim i As Integer
For i = 7 To Range("B" & Rows.Count).End(xlUp).Row
SolverReset
Solver.SolverOptions AssumeNonNeg:=False
SolverOk SetCell:="$Q$" & i, MaxMinVal:=1, ByChange:="$E$" & i, Engine:=1
SolverAdd CellRef:="$Q$" & i, Relation:=2, FormulaText:="$S$" & i
SolverSolve UserFinish:=True
Next
End Sub
Before you can run the macro you need to set a reference to solver in visual basic. Click on the "Visual Basic" icon ("Developers tab"), then click "Tools" and then click "References.
In the new window that opens find "Solver" and tick the box in front of it. Now you can run macro "SolverLoop" (Developres tab). Click "Macro" icon and then click "Run".
The macro loops through values from 7 to 94 (set by the Range("B" & Rows.Count).End(xlUp).Row) so that the range is set dynamically. Also found the solution for setting "Unconstrained variables to negative"
The uploaded file contains the macro but you probably need to set a reference in VBA to solver.
If there is an error in the formula in the L column there well normally not be a value set by solver in the corresponding row in the E column, still I found one exception in the uploaded file check E75 and L75 and Q75.
Have also uploaded the file with the new modified macro.
Alf
Bookmarks