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

rohitpatra007

New Member
Joined
Jun 3, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,618
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,175,794
Messages
5,899,535
Members
434,780
Latest member
andreeajc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top