Sort list numerically

[gmaxey]

I need to sort and fill a listbox with numbered items e.g., graphic files names 1-A, 2-B, 3-Z, 4-C ... 10-Flower ...246-D

So it looks like this:

1-A
2-B
3-Z
4-C
10-Flower
246-D

Most comprehensive sort processes I've found do great job sorting alphabetically or ascending/descending but in this case using those results in:

1-A
10-Flower
2-B
246-D
3-Z
4-C

I cobbled some code that works in pinch when I "know" the largest numerical list item:


Private Sub UserForm_Initialize()
Dim arrMixed() As String, arrSorted() As String
Dim lngItem As Long
Dim varParts
Dim lngCount As Long, lngCounter As Long, lngIndex As Long

'I have a list of numbered items e.g., numbered pictures in a folder e.g, 1-House.jpg, 2-Car.jgp, .... 10-backyard.jpg etc.
'and need to list them numerically in a listbox.

'Here is a mixed array of numbered items
arrMixed = Split("1-a,26-z,2-b,3-c,4-d,5-e,6-h,7-g,8-h,9-i,10-g", ",")
lngItem = 0
For lngCounter = 1 To 27 'Number larger than the largest expected numbered item.
For lngIndex = 0 To UBound(arrMixed)
varParts = Split(arrMixed(lngIndex), "-")
If varParts(0) = lngCounter Then
ReDim Preserve arrSorted(lngItem)
arrSorted(lngItem) = arrMixed(lngIndex)
lngItem = lngItem + 1
Exit For
End If
Next lngIndex
Next lngCounter
ListBox1.List = arrSorted
'Works fine except the largest numbered item is known e.g, 26 and I've hardcoded 27 in the loop. If the list
'contained for example 29-ac then it would not be included in the sorted list.
'Anybody have a better way so a mixed list with any numerical values would be sorted and listed least to greatest?
End Sub

I could modify the code to work with my example by using "250" or "500" or "5,000,000" but seems horribly inefficient.
Anybody have a better comprehensive method that doesn't require knowledge of the largest numerical list item or involve a large loop?

[SamT]

Actually you have a double sort
1-a, 2-a, 2-b, 3-a

Sometime the best way just doesn't look all that efficient, Fortunately, looping arrays is very fast.

IF your list is already in an array, that's great, but sometimes you need to add them on the fly. Add them to a Dictionary, using a counter as the Key. Then

Redim ArrRaw(1 to Dictionary.Count - 1, 1 to 2)
For i = 1 to Dictionary.Count
ArrRaw(i) = Split(Dictionary(i), "-")
Next

now check out http://stackoverflow.com/questions/152319/vba-array-sort-function

[gmaxey]

Sam,
I don't understand what you are trying to show me.

if I start out with arrMixed = Split("1-m,26-z,2-b,3-c,4-4,5-e,6-f,7-g,8-h,9-i,10-g", ",")

I need a sorted array like this:

1-m
2-b
3-c
4-r
5-e
6-f
7-g
8-h
9-i
10-j
26-z

It doesn't matter the order of the parts after the "-", the numbers need to be sorted in ascending order. Thanks

[SamT]

YW.

Any of the Sort methods in the link should work for you.

[gmaxey]

SamT,

Head must have been in the dark zone last night. You are correct the Natural Number sort worked fine. Thanks again.