Finding Max value for a series of data

[Melpp]

I want to find the highest 5 values from a column using the macro functions (because it all needs to be automated). This column contains about 4000 data points and so it needs to be very efficient. I then want to display those five values in a table beside the existing one.

 

[VoG]

Maybe like this

Sub Lrg()
Dim i As Long
For i = 1 To 5
    Range("B" & i).Value = WorksheetFunction.Large(Columns("A"), i)
Next i
End Sub

 

[Andrew Poulsom]

Can't you use a pivot table and show the top 5?

 

[njimack]

Something like this (assuming your data is in A1:A10, with output in column B)

Sub a()

Dim i As Long

For i = 1 To 5
    Cells(i, 2) = WorksheetFunction.Large(Range("A1:A10"), i)
Next i

End Sub

 

[Melpp]

I put in the following code but for some reason the line within the For loop has a error. Any ideas?

Sheets("Anterior").Select

For i = 1 To 5
Cells(4 + i, 9) = WorksheetFunction.Large(Columns("B"), i)
Next i

 

[VoG]

That works for me. What is the error message?