Bubble Sort (VBA) sorts text but not numbers

Below BubbleSort sorts text correctly.   However, apparently, it does not sort numeric values in ASC order.    That is, it sorts the following values ($310, $85, $75, and $125) in the order show below:

$125
$310
$75
$85

How do I change code to sort values as follows?
$75
$85
$125
$310

*****************************

Public Function BubbleSort(Strings() As String) As String()
   
    Dim A As Long
    Dim e As Long, f As Long, g As Long
    Dim i As String, j As String
    Dim m() As String, n() As String
   
    e = 1
    n = Strings
    Do While e <> -1
        For A = 0 To UBound(Strings) - 1
            i = n(A)
            j = n(A + 1)
            f = StrComp(i, j)
            If f <= 0 Then
                n(A) = i
                n(A + 1) = j
            Else
                n(A) = j
                n(A + 1) = i
                g = 1
            End If
        Next A
        If g = 1 Then
            e = 1
        Else
            e = -1
        End If
        g = 0
    Loop
    BubbleSort = n
   
End Function
ExpExchHelpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
If you put that data in a column that is formatted as currency (you won't need to manually add the dollar signs) and sort it, it will sort correctly.
0
Martin LissOlder than dirtCommented:
You could do it in a hidden sheet or in a helper column that you could clear after the sort.
0
Rgonzo1971Commented:
Hi,

pls try
Function SpecialBubbleSort(list() As String) As String() ' Format £123.45
    Dim First As Integer, Last As Long
    Dim i As Long, j As Long
    Dim Temp As String
    
    First = LBound(list)
    Last = UBound(list)
    For i = First To Last - 1
        For j = i + 1 To Last
            If Left(list(i), Len(list(i)) - 1) > Left(list(i), Len(list(j)) - 1) Then
                Temp = list(j)
                list(j) = list(i)
                list(i) = Temp
            End If
        Next j
    Next i
    BubbleSort = list()
End Function

Open in new window

Regards
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

ExpExchHelpAuthor Commented:
Rgonzo:

I copied the proposed VBA code into attached XLS.    It still doesn't sort correctly.   Did I make a mistake?

Thank you for any feedback in advance.

EEH
Combo-Box-Values----Automatically-Ad.xls
0
ExpExchHelpAuthor Commented:
Martin - ideally, I would like to say away from using a helper column.   Also, I need to ensure that text is still sorted as text.

EEH
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Being able to have both text and "numbers" sorted correctly, you would have to detect whether both cells contain "numbers", if so then compare as numbers.
How would you expect a mixed column to be sorted? Currency first, then text?
0
ExpExchHelpAuthor Commented:
Qlerno:   Thanks for chiming in... although mixed column is unlikely to have, in the event it occurs, I would prefer to have numeric first, followed by text.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Public Function BubbleSort(Strings() As String) As String()
    
    Dim A As Long
    Dim e As Long, f As Long, g As Long
    Dim i As String, j As String
    Dim m() As String, n() As String
    
    e = 1
    n = Strings
    Do While e <> -1
        For A = 0 To UBound(Strings) - 1
            i = n(A)
            j = n(A + 1)

            If Left(i,1) = "$" and Left(j,1) = "$" Then
                ' both currency values
                f = Val(Mid$(i,2)) - Val(Mid$(j,2))
            Else
                ' at least one is no currency value - just perform text compare
                f = StrComp(i, j)
            End If

            If f <= 0 Then
                n(A) = i
                n(A + 1) = j
            Else
                n(A) = j
                n(A + 1) = i
                g = 1
            End If
        Next A
        If g = 1 Then
            e = 1
        Else
            e = -1
        End If
        g = 0
    Loop
    BubbleSort = n
    
End Function

Open in new window

0
ExpExchHelpAuthor Commented:
Apparently, neither columns are sorted now.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The workbook you attached contains only numeric values, so lets try this function:
Public Function BubbleSort(Strings() As String) As String()
   
    Dim A As Long
    Dim e As Long, f As Long, g As Long
    Dim i As String, j As String
    Dim m() As String, n() As String
   
    e = 1
    n = Strings
    Do While e <> -1
        For A = 0 To UBound(Strings) - 1
            i = n(A)
            j = n(A + 1)
            If WorksheetFunction.IsNumber(i) Then
                If WorksheetFunction.IsNumber(j) Then
                    f = Val(i) - Val(j)     ' both numbers
                Else
                    f = -1                  ' number is always "smaller" than text
                End If
            ElseIf WorksheetFunction.IsNumber(j) Then
                f = 1                       ' number needs to get swapped with text
            Else
                f = StrComp(i, j)           ' compare strings
            End If
            
            If f > 0 Then
                n(A) = j
                n(A + 1) = i
                g = 1
            End If
        Next A
        If g = 1 Then
            e = 1
        Else
            e = -1
        End If
        g = 0
    Loop
    BubbleSort = n
   
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExpExchHelpAuthor Commented:
Strange... newly added values to the combo do not get stored in the Data Validation lists (for both column A and B) any longer.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
When I tested with your workbook, nothing worked, so I'm not surprised ;-),
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.