# Using VBA to create a variable "adjustable cells" array in Solver

1. ## Using VBA to create a variable "adjustable cells" array in Solver

Hi all!

I am creating a certain optimization system in Excel that uses Solver. Around 60 optimizations are carried out using Solver in combination with VBA. During these optimizations, the adjustable cells array varies. Therefore:

I would like to use VBA to determine the array used in Solver as the adjustable cells.

In other words: If cell A1 returns a value of 5, the number of rows used in the array of adjustable cells in Solver is 5. When cell B1 returns a value of 2, the number of columns used in the array of adjustable cells in Solver is 2.  Register To Reply

2. ## Re: Using VBA to create a variable "adjustable cells" array in Solver

Your best chance of getting help is for you to upload a sample file.

Alf  Register To Reply

3. ## Re: Using VBA to create a variable "adjustable cells" array in Solver

That's a good idea.

Solverproblem.xlsm

VBA is included!

I'm trying to solve a number of assignment problems (in the example I haven't added the permutation matrix boundaries (sum of all columns is 1 and sum of all rows is 1 of the permutation matrix) but added a different boundary: the sum of the permutation matrix is equal to the two cells that must determine the adjustable cell array.

In the example I have shown how I currently am solving this problem (with the cost value being dependent on the cell A2 and B2). The disadvantage of this solution is that Solver is still using the complete adjustable cell array, while only a part is necessary for the final solution. Because I am performing around 60 optimizations at an adjustable cell array of 14X14, the calculation time starts becoming a limiting factor (and that's why I want to create a variable adjustable cell array). When a smaller array for the adjustable cells is used, the calculation time drops significantly.

I am having a very hard time explaining my problem, I hope it is clear!  Register To Reply