Use VBA Function to calculate the rank

[ddddd123]

I want to create a VBA function to calculate the rank, like that

Function CALCULATERANKING(score As Double, list As Range) As Integer
    Dim row As Integer
    Dim column As Integer

    CalculateRanking = 1

    For row = 1 To list.Rows.count
        If list.Cells(row, 1).Value > score Then
            CalculateRanking = CalculateRanking + 1
        Else
            Exit For
        End If
    Next
End Function

But this can't meet my requirements. If I use CALCULATERANKING to calculate the table below it doesn't work. Should I use Lookup or something else to fix it?

Student​	Score​	Rank​
Jason	98	1
Peter	60	4
Johnny	70	3
Amy	28	5
Zoe	85	2

 

[Siddharth Rout]

Why do you need VBA for this?

Use the formula =RANK()

If you still want VBA then the below can solve your problem.

Range("C2:C6").Formula = "=RANK(B2,$B$2:$B$6)"

 

[ddddd123]

Can create a new function in a VBA module to acquire the ranking of a student? I understand this would be more troublesome, but I would like to know how to fix it.

But still thank for your answer!

 

[Siddharth Rout]

Can create a new function in a VBA module to acquire the ranking of a student? I understand this would be more troublesome, but I would like to know how to fix it.

But still thank for your answer!

You mean like this?

Option Explicit

Function CALCULATERANKING(score As Double, list As Range) As Integer
    CALCULATERANKING = Application.WorksheetFunction.Rank(score, list)
End Function

 

[ddddd123]

Yes! Thank you very much!!!

 

[Bugman1400]

How would you write the VBA code to include the COUNTIF function (with expanding reference) to deal with duplicate ranking.