VBA - Get index of nth largest value in an array

Question

I want to find the index of the nth largest value in an array. I can do the following but it runs into trouble when 2 values are equal.

fltArr(0)=31
fltArr(1)=15
fltArr(2)=31
fltArr(3)=52

For i = 0 To UBound(fltArr)
    If fltArr(i) = Application.WorksheetFunction.Large(fltArr, n) Then
        result = i
    End If
Next

n=1 ---> 3
n=2 ---> 2 (but I want this to be 0)
n=3 ---> 2
n=4 ---> 1

Answer 1

Uses a second array to quickly get what you want without looping through each element for every value of n

Sub test()

Dim fltArr(0 To 3)
Dim X
Dim n As Long
Dim lngPos As Long

fltArr(0) = 31
fltArr(1) = 15
fltArr(2) = 31
fltArr(3) = 52

X = fltArr

For n = 1 To 4
    lngPos = Application.WorksheetFunction.Match(Application.Large(X, n), X, 0) - 1
    Debug.Print lngPos
    X(lngPos) = Application.Max(X)
Next

End Sub

Answer 2

Edit:

Public Sub RunLarge()
Dim n%, i%, result%, count%
Dim fltArr(3) As Integer
Dim iLarge As Integer

fltArr(0) = 31:
fltArr(1) = 15:
fltArr(2) = 31:
fltArr(3) = 52
n = 1

Debug.Print " n", "iLarge", "result"

While n <= 4
    count% = n - 1
    iLarge = Application.WorksheetFunction.Large(fltArr, n)

    For i = 0 To UBound(fltArr)
        If fltArr(i) = iLarge Then
            result = i
            count% = count% - 1
            If count% <= 0 Then Exit For
        End If
    Next

    Debug.Print n, iLarge, result
    n = n + 1
Wend
End Sub

result:

 n            iLarge        result
 1             52            3 
 2             31            0 
 3             31            2 
 4             15            1