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

1. ## 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!

2. ## 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

3. ## 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. ## Re: Create a dynamic range for by changing variable cells box in solver

You are welcome and thanks for feedback

Alf

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

#### 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