How to automate the Rank worksheet function using VBA. Watch the video tutorial:

Here’s the complete VBA code for the tutorial:

Sub assignRanks()
Dim i As Long, lastrow As Long

lastrow = Application.WorksheetFunction.CountA(Range(“A:A”))

‘MsgBox lastrow

For i = 2 To lastrow
If Cells(i, 2) < 35 Then
Cells(i, 3) = “Fail”
Else
Cells(i, 3) = Application.WorksheetFunction.Rank(Cells(i, 2), Range(“B2:B” & lastrow), 1)
End If

Next i
End Sub

Sub assigningRankToSameRanks()
Dim i As Long, j As Long, lastrow As Long
Dim xtraValue

lastrow = Application.WorksheetFunction.CountA(Range(“A:A”))

For i = 2 To lastrow

For j = (i + 1) To lastrow

    If Cells(j, 3) = Cells(i, 3) Then
        xtraValue = (WorksheetFunction.Count(Range("B2:B" & lastrow)) + 1 - (WorksheetFunction.Rank(Cells(i, 2), Range("B2:B" & lastrow), 0)) - (WorksheetFunction.Rank(Cells(i, 2), Range("B2:B" & lastrow), 1))) / 2
        Cells(i, 3) = Cells(i, 3) + xtraValue
        Cells(i, 3) = Round(Cells(i, 3), 0)
    End If
Next j

j = i

Next i

End Sub

Sub AssignGrades()
Dim i As Long, lastrow As Long
lastrow = Application.WorksheetFunction.CountA(Range(“A:A”))
‘MsgBox lastrow

For i = 2 To lastrow
If Cells(i, 2) >= 90 Then
Cells(i, 6) = “A”
ElseIf Cells(i, 2) >= 80 Then
Cells(i, 6) = “B”
ElseIf Cells(i, 2) >= 70 Then
Cells(i, 6) = “C”

ElseIf Cells(i, 2) >= 60 Then
    Cells(i, 6) = "D"
ElseIf Cells(i, 2) >= 50 Then
    Cells(i, 6) = "E"
ElseIf Cells(i, 2) >= 35 Then
    Cells(i, 6) = "Pass"
Else: Cells(i, 6) = "Fail"

End If

Next i
End Sub

WorksheetFunction.Rank: Returns the rank of a number from a list of numbers. If you would sort the list in ascending or descending order, the rank of the number would be its position.

Syntax
expression.Rank (Arg1, Arg2, Arg3)

expression is a variable that represents a WorksheetFunction object.

Arg1 = Argument 1 is required and represents the number whose rank we wish to find.

Arg2 = Argument 2 is required and represents a range containing a list of numbers. Nonnumeric values in ref are ignored.

Arg3 = Argument 3 is optional and represents a variant variable that suggests how the rank has to be ordered – ascending or descending. If order is 0 (zero) or omitted, Excel ranks the number as if reference range were a list sorted in descending order. If order is 1 (one), Excel ranks number as if reference range were a list sorted in ascending order.

Rank gives duplicate numbers the same rank. Also, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 80 appears twice and has a rank of 3 as shown in the video, 82 would have a rank of 5 (no number would have a rank of 4).

Sometimes you might want to use a definition of rank that takes ties into account. In the above example, you would want a revised rank of 3.5 for the number 80. To do this, add the following correction factor to the value returned by Rank. This correction factor is appropriate both for the case where rank is computed in descending order (order = 0 or omitted) or ascending order (order = nonzero value).

Correction factor for tied ranks = =[COUNT(reference range) + 1 – RANK(number, reference range, 0) – RANK(number, reference range, 1)]/2 = xtraValue = (WorksheetFunction.Count(Range(“B2:B” & lastrow)) + 1 – (WorksheetFunction.Rank(Cells(i, 2), Range(“B2:B” & lastrow), 0)) – (WorksheetFunction.Rank(Cells(i, 2), Range(“B2:B” & lastrow), 1))) / 2

Next we round up the value of 3.5 to 4 as shown in the video and get the appropriate rank for Harry and Irfan.

Further reading: WorksheetFunction.Rank