#### Juggler_IN

##### Board Regular

- Joined
- Nov 19, 2014

- Messages
- 154

Code:

```
Public Function Rnk( _
ByVal x As Variant, _
ByRef y As Variant, _
ByVal z As Boolean) As Variant
' x is the item, y is the Range, and z is the rank order.
If TypeName(y) = "Range" Then
y = y.Value2
Else
End If
With CreateObject("System.Collections.ArrayList")
Dim i As Variant
For Each i In y
If VarType(i) >= 2# And VarType(i) <= 6# Then .Add Val(i)
Next i
.Sort
If z = False Then ' Descending Order
.Reverse
Else ' Ascending Order
End If
Rnk = Application.WorksheetFunction.Match(x, .ToArray, 0#) + 1
End With
End Function
```