I'm looking for VBA code that will find the maximum sum of a range (array) of cells with the following conditions:
1) Must have exactly 1 number picked in each column
2) Must not use any row more than once, but several rows will not get used.
3) If the number is 0, you can't use it. This "0" can be changed to a number like "-1000", so if that number is used in the sum, there is no way it would give a max sum since the largest number used in any cell is 31 and the max sum would be 31x17=527.
Im trying it out for a 5x8 array (5 columns, 8 rows). Once it is working, the final array would be 17x32 (17 columns, 32 rows) - hopefully it won't take the VBA code forever to go through that large of an array. Actually, I might limit the rows to like 19 instead of 32, as rows 20-32 probably wouldn't be used to get a max sum anyways.
My temporary array is (with the 26 in cell D3):
<TBODY>
</TBODY>
The max sum of this array is 26+11+19+12+15=83
but there are other valid combinations:
26+20+13+12+11=82
26+11+13+12+15=77
26+11+13+12+11=73
I'm looking for doing both of the following:
1) Highlight the cells used for the max sum
2) On a new sheet (or could just be an unused portion at the bottom of array on present sheet), list the row used for each column, i.e., 1 4 5 8 7 for the above array as this will help me distinguish between duplicate numbers in a column.
3) Display the sum - I can do this part with sum fn.
Also, how long do you think it will take to go through a 17x32 (or possibly 17x19) array? I tried something similar a year ago and I had to put in delay loops because it would lock up running the macro, but I have a better desktop now so it should run better.
Thank You for all help...
Marty
1) Must have exactly 1 number picked in each column
2) Must not use any row more than once, but several rows will not get used.
3) If the number is 0, you can't use it. This "0" can be changed to a number like "-1000", so if that number is used in the sum, there is no way it would give a max sum since the largest number used in any cell is 31 and the max sum would be 31x17=527.
Im trying it out for a 5x8 array (5 columns, 8 rows). Once it is working, the final array would be 17x32 (17 columns, 32 rows) - hopefully it won't take the VBA code forever to go through that large of an array. Actually, I might limit the rows to like 19 instead of 32, as rows 20-32 probably wouldn't be used to get a max sum anyways.
My temporary array is (with the 26 in cell D3):
26 | 18 | 15 | 0 | 31 |
0 | 0 | 0 | 0 | 0 |
0 | 0 | 13 | 0 | 0 |
0 | 11 | 0 | 0 | 0 |
0 | 0 | 19 | 0 | 11 |
0 | 0 | 0 | 0 | 0 |
0 | 20 | 0 | 0 | 15 |
25 | 0 | 0 | 12 | 0 |
<TBODY>
</TBODY>
The max sum of this array is 26+11+19+12+15=83
but there are other valid combinations:
26+20+13+12+11=82
26+11+13+12+15=77
26+11+13+12+11=73
I'm looking for doing both of the following:
1) Highlight the cells used for the max sum
2) On a new sheet (or could just be an unused portion at the bottom of array on present sheet), list the row used for each column, i.e., 1 4 5 8 7 for the above array as this will help me distinguish between duplicate numbers in a column.
3) Display the sum - I can do this part with sum fn.
Also, how long do you think it will take to go through a 17x32 (or possibly 17x19) array? I tried something similar a year ago and I had to put in delay loops because it would lock up running the macro, but I have a better desktop now so it should run better.
Thank You for all help...
Marty
Last edited: