Dynamically find variable cells range in excel solver

Hi,
I have the below VBA code, but I get an error when I pass the varRange variable to the range in SolverOk. My goal is to have the Variable Cells and the Constraints in Solver to have dynamic range.



Sub Solver3()

Dim lastRow As Long
Dim varRange As String

With ThisWorkbook.Sheets("Sheet11")
lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
varRange = "D18:D" & lastRow

SolverReset

SolverOk SetCell:="$C$31", MaxMinVal:=2, ValueOf:=0, varRange, _
Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:=varRange, Relation:=6, FormulaText:="AllDifferent"
SolverAdd CellRef:="$G$18", Relation:=2, FormulaText:="1"
SolverOk SetCell:="$C$31", MaxMinVal:=2, ValueOf:=0, varRange, _
Engine:=3, EngineDesc:="Evolutionary"
SolverOk SetCell:="$C$31", MaxMinVal:=2, ValueOf:=0, varRange, _
Engine:=3, EngineDesc:="Evolutionary"
SolverSolve userFinish:=True
End With

End Sub


Thanks,

Hi planner07,

Please Login or Register  to view this content.

m.s.

A recorded macro for a solver setup repeats it self so using MSDols tip to solve this problem I would think a bit shorter code should work as well.

Please Login or Register  to view this content.

the three solver engines can be added as "Engine:=1"or "Engine:=2" or "Engine:=3"

Alf

The max number of rows in an Excel spreadsheet is 1048578.

Using a "dim" statement in VBA for the rows they could be dimed as "Long" or "Integer".
Max number for "Long": 2147483648
Max number for "Integer": 32767

so it seems best to dim row number as Long since this exceeds the max number of rows in a spreadsheet. On the other hand the Excel Solver has an inbuild limit of about 200 constraints so if this limit is exceeded solver will stop with a message box "To many cells".

In the uploaded macro range starts at D18 and I would guestimate that solver can handle a total range of D18:D210.

Alf