+ Reply to Thread
Results 1 to 4 of 4

Create a dynamic range for by changing variable cells box in solver

  1. #1
    Registered User
    Join Date
    07-19-2017
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    4

    Create a dynamic range for by changing variable cells box in solver

    Hello all

    Long time viewer first time posting!

    I'm using solver at the moment to find the optimal way to fluctuate a series of data based on 7 or 8 constraints so the last number in the series hits a certain target based off these constraints.

    I'd like to be able to change the range for the "by changing variables cells" box in solver by basing it off a cell value.

    For example if the cell value in B4 has 21 in it, I want the by changing variables cells box to start at J3 and stop at J24, or if I enter 100 then stop at J103 etc. But always starts from J3.

    I have been able to set all my constraints from this cell value to only select a certain number of cells to constrain, but cannot get the "by changing variable cells" to work in the same fashion

    Any help greatly appreciated!
    Last edited by Ad896832; 07-19-2017 at 04:45 AM.

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Create a dynamic range for by changing variable cells box in solver

    As far as I can see your only option to do so is by using a macro.

    You can either run solver with a macro (not as difficult as it sounds) or you can use the fact that solver "understands" range names, still you need a macro

    Please Login or Register  to view this content.
    Right click on the sheet tab where you have your solver model and paste this code in the new window that opens. What this macro does is every time there is an input to cell B4 the value in B4 is added to the range starting at J3. Say that an input to B4 is 10 the you get a range called "ChngCells" going from J3 + 10 + 3 i.e. range "ChngCells" is defined as J3:J13

    So you can write ChngCells in the solver box for "By Changing variable cells" and every time you enter a value in B4 the J3:J?? range is changed according to the B4 value.

    Alf
    Last edited by Alf; 07-26-2017 at 02:03 PM.

  3. #3
    Registered User
    Join Date
    07-19-2017
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    4

    Re: Create a dynamic range for by changing variable cells box in solver

    Apologies for delayed response Alf- this worked perfectly thank you!

    Really appreciated!

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Create a dynamic range for by changing variable cells box in solver

    You are welcome and thanks for feedback

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Create dynamic variable range to use in function
    By olli.excel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2017, 07:24 PM
  2. Replies: 4
    Last Post: 12-13-2015, 04:35 PM
  3. create a variable i that counts number of cells in a range that start with 0
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2015, 08:30 PM
  4. Create a named range of cells BUT variable in reference to a cell value
    By peponfree in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-13-2014, 12:46 PM
  5. How to create a dynamic table based on a range of cells
    By leviathan86 in forum Excel General
    Replies: 1
    Last Post: 01-09-2013, 03:59 PM
  6. [SOLVED] 2nd value in cell range dependent on changing values other cells (dynamic?)
    By RUJedi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-16-2012, 07:34 PM
  7. Using VBA to create a variable "adjustable cells" array in Solver
    By Kybynn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-04-2012, 12:25 AM

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