Hello everyone.
For simplicity, I have a VBA loop which changes the range of cells being referenced during each iteration. For each loop I'm trying to find the largest 10 values and smallest 10 values from the range. The tricky part is I need to reference where those values are located in the range, because I need to start with the largest value and pair it with the smallest value that is also before this large value, and do this 10 times. So I need to find largest/smallest 10 values, but their "location" is important, even more so because after I find them, I need to edit a cell in the same row as the selected, acceptable values.
Hopefully this makes sense!
Now I could just take the range and use large and small functions like so:
Buuuut, I'll probably have to reference position with index-match or something, and if 2 large or small values are the same (will happen), I'll only reference one location for 2 distinct values. So what I think would be ideal, is to set each cell in the range to a collection or array and sort it. I like this idea, but I still want some way to reference the original position in the range even after I sort it, without having to do index-match or something with the same flaws. I know in collections you can use keys, but I also know sorting in collections is a whole nother mess and I'm trying to run this loops hundreds of time, so efficiency is important. Any ideas, such as a way to get around the flaws with index-match, or using collections or arrays?
Examples would be awesome. Thanks in advance to any responses!
For simplicity, I have a VBA loop which changes the range of cells being referenced during each iteration. For each loop I'm trying to find the largest 10 values and smallest 10 values from the range. The tricky part is I need to reference where those values are located in the range, because I need to start with the largest value and pair it with the smallest value that is also before this large value, and do this 10 times. So I need to find largest/smallest 10 values, but their "location" is important, even more so because after I find them, I need to edit a cell in the same row as the selected, acceptable values.
Hopefully this makes sense!
Now I could just take the range and use large and small functions like so:
Code:
WorksheetFunction.LargeBuuuut, I'll probably have to reference position with index-match or something, and if 2 large or small values are the same (will happen), I'll only reference one location for 2 distinct values. So what I think would be ideal, is to set each cell in the range to a collection or array and sort it. I like this idea, but I still want some way to reference the original position in the range even after I sort it, without having to do index-match or something with the same flaws. I know in collections you can use keys, but I also know sorting in collections is a whole nother mess and I'm trying to run this loops hundreds of time, so efficiency is important. Any ideas, such as a way to get around the flaws with index-match, or using collections or arrays?
Examples would be awesome. Thanks in advance to any responses!
