Hi Jason,

Try:

'==================
Private Sub UserForm_Initialize()
Dim arr As Variant, arr2 As Variant
Dim i As Long, j As Long
Dim swap As String
Dim strOut As String

arr = Array(27, 43, 51, 14, 33)
arr2 = arr
For i = LBound(arr) To UBound(arr) - 1
For j = i + 1 To UBound(arr)
If arr(i) arr(j) Then
swap = arr(i)
arr(i) = arr(j)
arr(j) = CLng(swap)
End If
Next j
Next i

For i = LBound(arr) To UBound(arr)
strOut = strOut & Application.Match(arr2(i), arr, 0)
Next i

TextBox1.Text = strOut

End Sub
'<<==================

---
Regards,
Norman


"jasonsweeney"
wrote in message
news:jasonsweeney.1wrtqb_1129089904.4892@excelforu m-nospam.com...

I have a VBA array of large numbers. I need to rank the items within
the array and drop them (in order) into a textbox. For example:

Input: Array(27, 43, 51, 14, 33)
Output in textbox should be: 2, 4, 5, 1, 3 (delimited here with
commas for informational purposes only. Actual output would be
"24513")

I would hope this would be simple in VBA, but looking at the posts
regarding ranking and sorting arrays, it looks more difficult than I
thought. And no, I don't want to put the data onto the worksheet and
rank it there. VBA code only please.

Here is the code I tried which does not work:
________________________________________
Private Sub CommandButton1_Click()
Myarray1 = Array(27, 43, 51, 14, 33)
For i = 1 To UBound(Myarray1)
Rank_num = Application.WorksheetFunction.Rank(Myarray1(i),
Myarray1, 0)
UserForm1.TextBox1.Value = UserForm1.TextBox1.Value & Rank_num
Next
End Sub
________________________________________

Suggestions appreciated.


--
jasonsweeney
------------------------------------------------------------------------
jasonsweeney's Profile:
http://www.excelforum.com/member.php...fo&userid=5222
View this thread: http://www.excelforum.com/showthread...hreadid=475286