The five largest values in a column

Question

My goal is to message box the five largest values in a column. But because I am using ">" the values in the column before L are not counted for La or Lb for that matter. For example:

7
8
5
3
6
2

L becomes 8, but La (the second largest) becomes 6 (but should've been 7), Lb (the third largest) becomes 2, and Lc =0, Ld=0.

Sub maxtest3()

Dim L As Integer, La As Integer, Lb As Integer, Lc As Integer, Ld As Integer
Dim a As Variant
L = 0
La = 0
Lb = 0
Lc = 0
Ld = 0


For Each a In Range("A1:A20")
If a.Value > L Then
    L = a.Value
        Else
        If a.Value > La Then
        La = a.Value
            Else
            If a.Value > Lb Then
            Lb = a.Value
                Else
                If a.Value > Lc Then
                Lc = a.Value
                    Else
                    If a.Value > Ld Then
                    Ld = a.Value
                        Else
                    End If
                End If
            End If
        End If
    End If
Next
                MsgBox (L & " " & La & " " & Lb & " " & Lc & " " & Ld)
End Sub

I know whoever sees this has the ability to solve the problem with one line of code but, please, restrain yourself from doing so for the sake of educating a newbie.

Answer 1

Your nested If ... Else ... statements could be easier written as a Select Case statement. It might even improve readability.

The problem was subsequent values overwriting previous ones. The existing values need to be pushed further down the queue before they are overwritten. While the following code is a bit verbose, it should adequately demonstrate the solution.

Sub maxtest3()
    Dim L As Variant, a As Range, rng As Range

    Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    ReDim L(1 To 5)

    For Each a In rng
        Select Case a.Value
            Case Is > L(1)
                L(5) = L(4)
                L(4) = L(3)
                L(3) = L(2)
                L(2) = L(1)
                L(1) = a.Value
            Case Is > L(2)
                L(5) = L(4)
                L(4) = L(3)
                L(3) = L(2)
                L(2) = a.Value
            Case Is > L(3)
                L(5) = L(4)
                L(4) = L(3)
                L(3) = a.Value
            Case Is > L(4)
                L(5) = L(4)
                L(4) = a.Value
            Case Is > L(5)
                L(5) = a.Value
        End Select
    Next a

    MsgBox Join(L, Chr(32))

End Sub

I've changed your Lx vars to a simple one dimensional array. This allows the use of the Join Function to simplify the string concatenation to the MsgBox.

Answer 2

Sub test()

Range("A1", "A6").Copy 'adjust the column you want to sort
Range("B1", "B6").PasteSpecial 'adjust to a free column

Columns("B").Sort key1:=Range("B1"), _ 'adjust
  order1:=xlDescending, Header:=xlNo

Dim i As Integer
Dim str As String

For i = 1 To 5

    str = str & " " & Cells(i, 2).Value 'adjust to the column you pasted too

Next

Range("B1", "B6").Clear 'adjust

MsgBox str

End Sub