Rank If Passed

Sub Marks_rank()
Dim i As Integer
Dim k As String
For i = 2 To Sheet1.Range("B1048576").End(xlUp).Row
k = Application.WorksheetFunction.Rank(Cells(i, "B"), Range("B:B"), 0)
If Cells(i, "C") <> "Failed" Then
Cells(i, "D") = k
End If
Next
End Sub