Solver and Dynamic Named Ranges

[Ritzyy]

Hi,

Is it possible to use a dynamic named range to define a constraint or the variable cells in solver? Currently I have a name "Iterations" defined as =OFFSET($I$6,,,COUNTA($I:$I)-1), however, when I go to include this on the LHS of a constraint, as soon as I click 'Add' the constraint, the name range simple changes to $I$6:$I$22 and no longer references the name.

Any help would be appreciated!

 

[Worf]

Hi

You can use VBA to dynamically define the range and execute Solver:

Sub Dyn()
SolverOk SetCell:="$D$6", MaxMinVal:=1, ValueOf:="0", _
ByChange:="$b$3:$b$" & Evaluate("=counta($b:$b)")
solversolve userfinish:=True
End Sub

 

[Ritzyy]

Hi

You can use VBA to dynamically define the range and execute Solver:

Sub Dyn()
SolverOk SetCell:="$D$6", MaxMinVal:=1, ValueOf:="0", _
ByChange:="$b$3:$b$" & Evaluate("=counta($b:$b)")
solversolve userfinish:=True
End Sub

Epic, thanks mate!

 

[Worf]

You are welcome!