I have a spreadsheet that vba populates for me. The columns are always in descending order. I need to find the array in column AL where the value of the cells are 1. I then need to use that array to find the values in column AV for the corresponding row #s from the first array. Once I have the second array, I find the max value. Whatever row the max value is on, I need to get the number from the corresponding row in column AS.
For Example: 1. Column AL - The array where the cell values are 1 would be AL36:AL42
2. From the row numbers of the first array (AL36:AL42) i need to find the values of cells in column AV which would be in array AV36:AV42
3. I need the highest value in the second array which would be cell AV36
4. I then need to get the value of row 36 for column AS, which would be 9
5. I then need to populate that number (9) in cell B4.
I hope I have explained this well enough.
I am attaching a spreadsheet as well.
Thanks in advance!!