Results 1 to 2 of 2

VBA for Dynamic range Solver generates error-Not variable cells but marked All Diff & Int

Threaded View

  1. #1
    Registered User
    Join Date
    06-03-2021
    Location
    Bhubaneswar, India
    MS-Off Ver
    MS Office 365
    Posts
    1

    VBA for Dynamic range Solver generates error-Not variable cells but marked All Diff & Int

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

    I want the solver to set its "by changing variable" (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 should define the dynamic range of column J. T9 is B rows 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
    Last edited by AliGW; 06-03-2021 at 07:46 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Create a dynamic range for by changing variable cells box in solver
    By Ad896832 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-26-2017, 02:03 PM
  2. Replies: 2
    Last Post: 03-20-2016, 04:54 AM
  3. set variable to 0 if a function generates an error
    By luv2glyd in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-03-2016, 08:43 PM
  4. [SOLVED] Obj Variable or with variable not set error on emailing diff jpg data to diff recipients
    By BigDawg15 in forum Outlook Programming / VBA / Macros
    Replies: 9
    Last Post: 11-16-2014, 12:51 PM
  5. Range Select generates 400 error code
    By BRISBANEBOB in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-17-2013, 02:26 AM
  6. range.offset generates a Run Time error 1004
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2006, 12:50 PM
  7. Dynamic range (object variable error)
    By Mslady in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-01-2005, 04:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1