Thread: 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...

Sub Adjust()

Dim c As Range
Dim Cell1 As Range
Dim Cell2 As Range
Dim rng As Range
Dim irow As Integer
Dim icol As Integer

Dim i As Double
Dim j As Variant
Dim k As Variant
Dim l As Variant
Dim m As Double
Dim n As Double
Dim o As Double
Dim p As Double
Dim q As Double



For icol = 1 To 3
m = 1

    
        ' Loops through the cells in the column and counts how many are above the criterion value
        For irow = 1 To 14
        j = irow + 30
        
        Set curCell = Worksheets("Sheet1").Cells(irow, icol)
                If Abs(curCell.Value) > Cells(irow, 20).Value Then
        
                m = m + 1
        
                End If
        
        Next irow

        
        
        ' j = a constant so that the values can be pasted below.
        'o = a constant  for the large function, allowing the program to determine the next value in the list. It adds 1 to m for use
        ' with the large function.
        'q = The large function, which returns the ith largest value. So if the the value that needs to be adjusted is the third highest in the column,
        '       this selects the fourth highest and adds 1.
        
        For irow = 1 To 14
        
        Set Cell1 = Cells(irow, icol)
        Set Cell2 = Cells(14, icol)
        Set rng = Range(Cell1, Cell2)
        
        
        j = irow + 30
        o = m + 1
        p = Application.WorksheetFunction.Large(rng, m)
        q = Application.WorksheetFunction.Large(rng, o) + 1
        
        
        'This is where each cell is selected in turn. If it is the mth largest value, then p is selected (which is+1 above the next highest value.
        ' If not, the current cell value is selected. These are then pasted into row 30 onwards, using the constant j.
        Set curCell = Worksheets("Sheet1").Cells(irow, icol)

                If Abs(curCell.Value) = p Then Worksheets("Sheet1").Cells(j, icol).Value = p
        
                Else: Worksheets("Sheet1").Cells(j, icol).Value = (curCell.Value)
                
                End If
                
        m = m - 1
         
        Next irow
        
   
Next icol

End Sub

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