VBA Sort AlphaNumeric

Question

I have a function in VBA that is supposed to sort text based on a "Bubble sort". If the text were just text then it would be fine, but my text is actually an alpha numeric string. I tried to rewrite it to account for the number part but something is still off and I can't seem to figure out what. Please help!!

Dim alphaCurr As String
Dim alphaNext As String
Dim rowCurr As FsChartRow
Dim rowNext As FsChartRow
Dim c As Integer
Dim n As Integer
Dim vTemp As Variant

For c = 1 To rows.count - 1
    Set rowCurr = rows(c)
    alphaCurr = GetAlpha(rowCurr.label)
    For n = c + 1 To rows.count
        Set rowNext = rows(n)
        alphaNext = GetAlpha(rowNext.label)

        If alphaCurr > alphaNext Then
            Set vTemp = rows(n)
            rows.Remove n
            rows.Add vTemp, , c
        End If

    Next n
Next c

Dim numCurr As Integer
Dim numNext As Integer
Dim loopCount As Integer

    For c = 1 To rows.count - 1
        Set rowCurr = rows(c)
        alphaCurr = GetAlpha(rowCurr.label)
        numCurr = GetNumeric(rowCurr.label)
        For n = c + 1 To rows.count
            Set rowNext = rows(n)
            alphaNext = GetAlpha(rowNext.label)
            numNext = GetNumeric(rowNext.label)

            If alphaCurr = alphaNext Then
                If numCurr > numNext Then
                    Set vTemp = rows(n)
                    rows.Remove n
                    rows.Add vTemp, , c
                End If
            End If

        Next n
    Next c

The results I am getting are as follows:

"BK1" "BK2" "FB1" "FB4" "FB3" "FB5" "FB6" "FB2" "FJ2" "FJ1" "FJ3" "FJ4" "..." "FJ15" "RB1" "H1" "H2"

Thank for your help!

`I tried to rewrite it to account for the number part` - why simple buble sort doesn't suit? — Dmitry Pavliv, Apr 24 '14 at 18:16
Because it out putted the wrong order BK2 would be before BK1 etc... Unless I was doing it wrong. I followed an example I found on StackExchange. — Richard S., Apr 24 '14 at 22:53
you're doing something wrong..check [this](http://www.google.ru/url?q=http://social.msdn.microsoft.com/Forums/en-US/830b42cf-8c97-4aaf-b34b-d860773281f7/sorting-an-array-in-vba-without-excel-function%3Fforum%3Disvvba&sa=U&ei=65ZZU8KOI8G34ATCg4Fw&ved=0CB0QFjAA&sig2=TTeYAD786rbpmyliPg27sg&usg=AFQjCNHk35L97w5XwaHdZ6UXIjbEv1uXfg) — Dmitry Pavliv, Apr 24 '14 at 22:58

score 0 · Accepted Answer · answered Apr 25 '14 at 18:03

I have found a solution to my problem. I still could not get the bubble sort to work, so I created my own, that takes no more time to run then the bubble sort. I'll post in case it helps anyone.

Private Function SortFsChartRow(collection As collection) As collection

Dim min As Integer
Dim max As Integer
Dim x As Integer
Dim y As Integer
Dim rowCurr As FsChartRow
Dim numCurr As Integer
Dim rowMin As FsChartRow
Dim rowMax As FsChartRow

Dim search As Integer

Dim sorted As collection
Set sorted = New collection
min = 100
max = 0

For x = 1 To collection.count
    Set rowCurr = collection(x)
    numCurr = GetNumeric(rowCurr.label)
    If numCurr > max Then
        max = numCurr
        Set rowMax = rowCurr
    End If

    If numCurr < min Then
        min = numCurr
        Set rowMin = rowCurr
    End If
Next x

search = min

For y = 0 To max
    For x = 1 To collection.count
        Set rowCurr = collection(x)
        numCurr = GetNumeric(rowCurr.label)
        If numCurr = search Then
            sorted.Add rowCurr
            Exit For
        End If
    Next
    search = search + 1
Next y

Set SortFsChartRow = sorted

End Function

VBA Sort AlphaNumeric

1 Answers1