I've got an enfuriating problem using the Solver in VBA.
I use a range to hold my "SetCell" by "Varying" values. I then loop over the range rows, resetting the solver each time and setting the cell references to vary etc. then adding a constraint. Finally I run the Solver. Trouble is the cell references are not being set as I loop over the range...
Code
Private Sub cmdRun_Click()
Dim rng As Range
Dim Row As Integer
Dim ref1 As String, ref2 As String
Set rng = Range("M7:M32")
rng.Value = 2
For Row = 1 To rng.Rows.Count
'Set value refrence...
ref1 = rng(Row, 1).Offset(0, 2).Address(False, False, xlA1)
'Vary reference...
ref2 = rng(Row, 1).Address(False, False, xlA1)
'========================================================
'Reset the solver...
Call SolverReset
'Solver Options...
Call SolverOptions(MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=True, Convergence:=0.0001, AssumeNonNeg:=False)
'Cells to vary...
Call SolverOk(SetCell:=Range(ref1), MaxMinVal:=2, ValueOf:="0", ByChange:=Range(ref2))
'Add a constraint...
Call SolverAdd(CellRef:=Range(ref2), Relation:=3, FormulaText:="0.0001")
'Solve...
Call SolverSolve(True)
'=========================================================
Next Row
End SubCan anyone see what I'm doing wrong?