VBA sort of an alphanumeric array 1

[steinfeld]

I am trying to sort the record source of a listbox every time something is added to it from another listbox. It works when I only use alphanumerics with the numeric part below 10 but when I go above 9 it does not sort correctly. Is ther some way to treat the numeric part as a number?

example:
one diemensonal array contents
b-4
a-10
c-1
a-3
a-1

result
a-1
a-10
a-3
b-4
c-1

desired result
a-1
a-3
a-10
b-4
c-1

Here is the procedure I am useing:

Private Sub List12_DblClick(Cancel As Integer)


List10.AddItem ("IER-" + List12.Value)

myArray = Split(List10.RowSource, ";")

For lLoop = 0 To UBound(myArray) - 1
For lLoop2 = lLoop To UBound(myArray)
If UCase(myArray(lLoop2)) < UCase(myArray(lLoop)) Then
str1 = myArray(lLoop)
str2 = myArray(lLoop2)
myArray(lLoop) = str2
myArray(lLoop2) = str1
End If
Next lLoop2
Next lLoop

List10.RowSource = Join(myArray, ";")

End Sub

 

[stix4t2]

Can you format the number like format(num, "00") so the numbers will sort alpha numeric.
a-01
a-03
a-10

You could also try parsing the letter from the number and then compare letters to letters and numbers to numbers.

 

[steinfeld]

The format() did not work (code below).

I guess I will need to code it to look at the numbers and letters differently.

For lLoop = 0 To UBound(myArray) - 1
For lLoop2 = lLoop To UBound(myArray)
If Format(myArray(lLoop2), "00") < Format(myArray(lLoop), "00") Then
str1 = myArray(lLoop)
str2 = myArray(lLoop2)
myArray(lLoop) = str2
myArray(lLoop2) = str1
End If
Next lLoop2
Next lLoop

 

[stix4t2]

it may be easier to split the values into variables and then make the comparisons.
dim alpha1 as string, alpha2 as string
dim num1 as integer, num2 as integer

For lLoop = 0 To UBound(myArray) - 1
For lLoop2 = lLoop To UBound(myArray)
alpha1 = left(myarray(lLoop),1)
alpha2 = left(myarray(lLoop2),1)
if( alpha1 < alpha2 ) then
num1 = cint(mid(myarray(lLoop),3))
num2 = cint(mid(myarray(lLoop2),3))
if( num1 < num2 ) then
str1 = myArray(lLoop)
str2 = myArray(lLoop2)
myArray(lLoop) = str2
myArray(lLoop2) = str1
End If
End If
Next lLoop2
Next lLoop

This based on only the first character being alpha, followed by a '-' and then followed by the number.

 

[PHV]

Typed, untested:
For lLoop = 0 To UBound(myArray) - 1
For lLoop2 = lLoop To UBound(myArray)
alpha1 = Left(myArray(lLoop), InStr(myArray(lLoop), "-") - 1)
alpha2 = Left(myArray(lLoop2), InStr(myArray(lLoop2), "-") - 1)
num1 = Val(Mid(myArray(lLoop), InStr(myArray(lLoop), "-") + 1))
num2 = Val(Mid(myArray(lLoop2), InStr(myArray(lLoop2), "-") + 1))
If UCase(alpha2) < UCase(alpha1) _
Or (UCase(alpha2) = UCase(alpha1) And num2 < num1) Then
str1 = myArray(lLoop)
str2 = myArray(lLoop2)
myArray(lLoop) = str2
myArray(lLoop2) = str1
End If
Next lLoop2
Next lLoop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[steinfeld]

That did the trick thanks.

 

[ProgramError]

myArray = Split(List0.RowSource, ";")

For formatloop = 0 To UBound(myArray) - 1
If Len(myArray(formatloop)) = 3 Then
myArray(formatloop) = Left(myArray(formatloop), 2) & "0" & Mid(myArray(formatloop), 3)
End If
Next

For lLoop = 0 To UBound(myArray) - 1
For lLoop2 = lLoop To UBound(myArray)
If myArray(lLoop2) < myArray(lLoop) Then
' swop the positions
str1 = myArray(lLoop)
str2 = myArray(lLoop2)
myArray(lLoop) = str2
myArray(lLoop2) = str1
End If
Next lLoop2
Next lLoop

List0.RowSource = Join(myArray, ";")

Ian Mayor (UK)
Program Error
Programming is 10% coding and 90% error checking.