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