Rank items in VBA Array?

[jasonsweeney]

I have a VBA array of large numbers. I need to rank the items withi
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 wit
commas for informational purposes only. Actual output would b
"24513")

I would hope this would be simple in VBA, but looking at the post
regarding ranking and sorting arrays, it looks more difficult than
thought. And no, I don't want to put the data onto the worksheet an
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

 

[Jason Sharp]

hi dude. I've never used the Array method before, but maybe I can help. It's
apparent that your desired list return is an algo for the first "digit" in
each number of the assigned element. Additionally, I see no logical
sequential pattern occurring. Finally, I have never used the Rank function
before, I'm assuming that you wrote a function with assigned parameters. That
said...

Are you sure your LOOP CODE is correct? You wrote "For i = 1 To
UBound(Myarray1)". Check the info on the Array method, your counter might
start at 0 not 1. A working procedure that returns the wrong information in
this circumstance would return 4, 5, 1, 3 [w/o the 2]. Traditionally variable
arrays begin with an element 0.

 

[jasonsweeney]

There is no significance to the first digits of the example I used. For
example:

Input: Array(43, 765, 456544, 42, 44)
Output in textbox should be: 2, 4, 5, 1, 3

Not sure about the counter issue, though it easily fixed if it starts
with element "0"....My question is how to do the ranking. The
Application.WorksheetFunction.Rank does not appear to work in the
manner I attempted.

 

[Patrick Molloy]

you could drop the array to a sheet & then SORT it. I find that the sorting
algorithm in Excel is far superior to anything that I built in VB!

try this

Option Explicit
Sub test()
Dim ar1 As Variant
Dim ar2 As Variant
ar1 = Array(27, 43, 51, 14, 33)
ar2 = SortArray(ar1)
' ar2 is now sorted

End Sub

Function SortArray(ByVal ar As Variant) As Variant
Dim index As Long
Dim depth As Long
depth = UBound(ar, 1) + 1
With Range("A1").Resize(depth)
.Value = WorksheetFunction.Transpose(ar)
.Sort Range("A1"), xlAscending, Header:=xlNo
SortArray = .Value
End With
End Function

 

[Norman Jones]

Hi Jason,

One way:
'===============>>
Private Sub UserForm_Initialize()
Dim arr As Variant
Dim i As Long, j As Long
Dim swap As String
Dim strOut As String

arr = Array(27, 43, 51, 14, 33)

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 & arr(i)
TextBox1.Text = strOut
Next i

End Sub
'===============>>

---
Regards,
Norman



"jasonsweeney" <[email protected]>
wrote in message
news:[email protected]...

 

[Norman Jones]

Hi Jason,

Please ignore!

---
Regards,
Norman

Hi Jason,

One way:
'===============>>
Private Sub UserForm_Initialize()
Dim arr As Variant
Dim i As Long, j As Long
Dim swap As String
Dim strOut As String

arr = Array(27, 43, 51, 14, 33)

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 & arr(i)
TextBox1.Text = strOut
Next i

End Sub
'===============>>

---
Regards,
Norman



"jasonsweeney" <[email protected]>
wrote in message

 

[Norman Jones]

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" <[email protected]>
wrote in message
news:[email protected]...