Using the LARGE function to adjust the ith largest values

Hi everyone,

Excuse the crude post, I am new to VBA (and forums)!
I am trying to write a program to examine the values in a worksheet column and adjust any values that are larger than a specific criterion value (that is also in the sheet). They have to be adjusted in order so that the highest value is still the highest and the second highest is still the second highest etc.

Just as a rough example, with the following set of numbers, I would like the code to adjust the values 57 and 60 because they are above the criterion value of 8. 57 Needs to be adjusted to the next highest value (3) and then 60 needs to be adjusted to the value above that (4).

1
2
2
1
2
57
60

8


I thought the best way to do this was to create a variable to count how many values are above the criterion and use this in conjunction with the LARGE function. This way, each value can be selected and adjusted in reference to the next highest value accordingly. The code is as follows...

Please Login or Register  to view this content.

There are a couple of issues with this code however.
Firstly, the LARGE function doesn't seem to like having a variable as its second argument. Also, once the if statement detects one of the high values, the alteration applies to all of the following values even if they are not above the criterion (i.e. every value is adjusted once one of the higher values has been encountered).

I hope this makes sense I would really appreciate some help/suggestions of a better way to accomplish the task.

Thanks in advance,
Kyle