Juggler_IN
Active Member
- Joined
- Nov 19, 2014
- Messages
- 349
- Office Version
- 2003 or older
- Platform
- Windows
I wanted help in modifying a RANK UDF to output ORDER values instead of RANK values as per the attached image. Thereby, instead of ranks 2,3,1 for data 5,6,4, the UDF should output the order 3,1,2.
The custom Rank UDF is:
The custom Rank UDF is:
VBA Code:
Public Function Rankvalue( _
ByVal u As Variant, _
ByRef v As Variant, _
Optional ByVal w As Boolean = True) As Variant
Dim x As Variant, y As Variant
On Error GoTo ErrHlr
If VBA.TypeName(v) = "Range" Then
v = v.Value2
Else
End If
With CreateObject("System.Collections.ArrayList")
For Each x In v
If VBA.VarType(x) >= 2& And VBA.VarType(x) <= 6& Then
.Add VBA.Val(x)
Else
GoTo ErrHlr
End If
Next x
.Sort ' Ascending Order
If w = False Then ' Descending Order
.Reverse ' Ascending Order
Else
End If
y = Application.WorksheetFunction.Match(u, .ToArray, 0&)
Rankvalue = y
Exit Function
End With
ErrHlr:
Rankvalue = "?"
Exit Function
End Function