Hello,
I have a model where I need to decide which of 30 fixed variables are going to be used in a final calculation. I set a swith to either turn each one on or off, then the model gives me the answer. I need to find the combination of these 30 variables that maximizes my final answer. The answer is not intuitive as if I use all the vairables I have blown my "budget". If I do this manually, it will require me to test 900 (30^2) possible combinations.
I have setup my model with a range of 30 contiguous cells that are contained in column B and I can set each of these cells to either 1 or 0 (true or false) to switch the variables in each of the adjacent cells in column C either on or off. If the cell is "off" it will not be used in the calculation.
What I am trying to do is to write a small VBA program that will cycle through all 900 combinations then provide the "range" of varible settings that gives the best answer. e.g. (0,0,0,1,1,1,0,0,0,0....1,1,1,0)
I am somewhat familiar with VBA but am stumped at how I would do this. In an ideal world I am somehow using a range of values for each of the 300 iterations, then capturing the best range.
Thanks!
I have a model where I need to decide which of 30 fixed variables are going to be used in a final calculation. I set a swith to either turn each one on or off, then the model gives me the answer. I need to find the combination of these 30 variables that maximizes my final answer. The answer is not intuitive as if I use all the vairables I have blown my "budget". If I do this manually, it will require me to test 900 (30^2) possible combinations.
I have setup my model with a range of 30 contiguous cells that are contained in column B and I can set each of these cells to either 1 or 0 (true or false) to switch the variables in each of the adjacent cells in column C either on or off. If the cell is "off" it will not be used in the calculation.
What I am trying to do is to write a small VBA program that will cycle through all 900 combinations then provide the "range" of varible settings that gives the best answer. e.g. (0,0,0,1,1,1,0,0,0,0....1,1,1,0)
I am somewhat familiar with VBA but am stumped at how I would do this. In an ideal world I am somehow using a range of values for each of the 300 iterations, then capturing the best range.
Thanks!
