excel - Select the top 10 maximum values

Question

I need help writing a loop that finds the 10 highest values in column "F". For each of the 10 highest values that are selected, I want to paste that value (as well as the associated values in column C, D, and E) in another spreadsheet.

Thanks

Answer 1

The Aggregate function is designed to ignore error values (among other things). Here's a SUB Aggregate and Large to get a threashold to triggger your copy code

Sub GetTop10(r As Range)
    Dim v As Variant
    Dim t As Variant
    Dim i As Long

    ' 14 = function LARGE
    '  6 = ignore error values
    ' 10 = get 10'th largest value
    t = Application.WorksheetFunction.Aggregate(14, 6, r, 10)
    v = r
    For i = 1 To UBound(v, 1)
        If Not IsError(v(i, 1)) Then
            If v(i, 1) >= t Then
                ' copy r.cells(i,-2).resize(1,4) to your other sheet
            End If
        End If
    Next
End Sub