VBA for dynamic range in Solver generates error- "Cells which are not variable cells are marked as All Diff, Int & binary"

[rohitpatra007]

Hi everyone,
I need immediate help in solving the below. Thanks in Advance

I want the solver to set its "by changing" (lets say column J) and constraint range depending on the number of rows of another column, lets say B (which is not a by changing variable). The generated solutions of "by changing variable" (column J) should be All different and integer, and should have a higher bound (same as the number of rows of B ). The number of rows in column B changes regularly because it is referenced to another function. The solver range should dynamically change according to B.

I don't have much experience in VBA but I tried coding in two methods and both of them show the same error.

But it generates an error saying -" Perhaps some cells that are not variable cells are marked as integer, binary or all different"

Column J is the "by changing variable". Rows in B column define the dynamic range and lr "T9" is its count value.

Method 1
Sub Solver4()
Dim lr As Long
lr = Cells.Range("T9").Value
SolverOk SetCell:="$T$8", MaxMinVal:=1, ValueOf:=0, ByChange:="$J$3:$J$" & Evaluate("=counta($B:$B)"), Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:="$J$3:$J$" & Evaluate("=counta($B:$B)"), Relation:=6, FormulaText:="AllDifferent"
SolverAdd CellRef:="$J$3:$J$" & Evaluate("=counta($B:$B)"), Relation:=3, FormulaText:="1"
SolverAdd CellRef:="$J$3:$J$" & Evaluate("=counta($B:$B)"), Relation:=1, FormulaText:="lr"
SolverAdd CellRef:="$J$3:$J$" & Evaluate("=counta($B:$B)"), Relation:=4, FormulaText:="integer"
SolverSolve
End Sub

Method 2
Sub Solver4()
Dim lr As Long
lr = Cells.Range("T9").Value
SolverOk SetCell:="$T$8", MaxMinVal:=1, ValueOf:=0, ByChange:="$J$3:$J$" & 2 + lr, Engine:=3, EngineDesc:="Evolutionary"
SolverAdd CellRef:="$J$3:$J$" & 2 + lr, Relation:=6, FormulaText:="AllDifferent"
SolverAdd CellRef:="$J$3:$J$" & 2 + lr, Relation:=3, FormulaText:="1"
SolverAdd CellRef:="$J$3:$J$" & 2 + lr, Relation:=1, FormulaText:="lr"
SolverAdd CellRef:="$J$3:$J$" & 2 + lr, Relation:=4, FormulaText:="integer"
SolverSolve
End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA for Dynamic range Solver generates error-Not variable cells but marked All Diff & Int
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.