Consulting

Results 1 to 4 of 4

Thread: Help with sorting arrays numerically

  1. #1

    Help with sorting arrays numerically

    I am trying to figure out how to sort an array numerically. I have no problem getting it to work alphabetically; but numerically, it does not seem to work.

    I have below, for example, a list of four numbers divided by commas, but I can't understand why "actual" at the bottom of this code does not give "3, 4, 5, 11" instead of "11, 3, 4, 5". Many thanks in advance for any help on this matter!

    Dim actual As String
    Dim arr As Variant
    Dim overall As String
    Dim strTemp As Long
    Dim i As Long
    Dim j As Long
    Dim lngMin As Long
    Dim lngMax As Long
     
    overall = "3, 4, 11, 5"
    arr = Split(overall, ", ")
    
    lngMin = LBound(arr)
    lngMax = UBound(arr)
    For i = lngMin To lngMax - 1
      For j = i To lngMax
        If arr(i) > arr(j) Then
          strTemp = arr(j)
          arr(j) = arr(i)
          arr(i) = strTemp
        End If
      Next j
    Next i
    
    For i = 0 To UBound(arr)
      actual = Join(arr, ", ")
    Next i
    Last edited by Davecogz84; 04-25-2021 at 03:31 PM. Reason: Added code tags & formatting

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,311
    Location
    For example:
    Dim DataArray()
    DataArray() = Array("3", "4", "11", "5")
    WordBasic.SortArray DataArray()
    MsgBox Join(DataArray(), ", ")
    Alternatively, to accept data in your original format:
    Sub DemoX()
    Dim StrData As String, i As Long, j As Long, DataArray()
    StrData = "3, 4, 11, 5"
    j = UBound(Split(StrData, ", ")): ReDim DataArray(j)
    For i = 0 To j
      DataArray(i) = Split(StrData, ", ")(i)
    Next
    WordBasic.SortArray DataArray()
    MsgBox Join(DataArray(), ", ")
    End Sub
    Note: To use WordBasic.SortArray to sort a text array, you need to define DataArray() as a string.

    PS: When posting code, please structure your code and use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.
    Last edited by macropod; 04-26-2021 at 01:48 AM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,828
    Location
    Actually your macro was performing exactly how it was coded.

    The biggest issue was that you were comparing and sorting strings, not numbers (screen shot)

    So "11,3,4,5" was correct since "11" is < "3", although 3 is < 11

    Your "j" start was off by 1 also

    ALso, that not the way Join() works -- no looping

    So look at SortDemo() and compare to SortDemoOriginal

    Capture.JPG


    Option Explicit
    
    
    Sub SortDemo()
        Dim actual As String
        Dim arr As Variant
        Dim overall As String
        Dim strTemp As String  '  <<<<<<<<<<<<<<<<<<
        Dim i As Long
        Dim j As Long
         
        overall = "3, 4, 11, 5"
        arr = Split(overall, ",")
        
        
        For i = LBound(arr) To UBound(arr) - 1
            For j = i + 1 To UBound(arr)             '  <<<<<<<<<<<<<<<<<<
                If CLng(arr(i)) > CLng(arr(j)) Then    '  <<<<<<<<<<<<<<<<<<
                    strTemp = arr(j)
                    arr(j) = arr(i)
                    arr(i) = strTemp
                End If
            Next j
        Next i
        
        actual = Join(arr, ", ")   '  <<<<<<<<<<<<<<<<<<
    
    
        MsgBox actual
    End Sub
    
    
    
    
    Sub SortDemoOriginal()
        
        Dim actual As String
        Dim arr As Variant
        Dim overall As String
        Dim strTemp As Long     '   <<<<<<<<<<<<<<<<<<
        Dim i As Long
        Dim j As Long
        Dim lngMin As Long
        Dim lngMax As Long
         
        overall = "3, 4, 11, 5"
        arr = Split(overall, ", ")
        
        lngMin = LBound(arr)
        lngMax = UBound(arr)
        For i = lngMin To lngMax - 1
            For j = i To lngMax     '   <<<<<<<<<<<
                If arr(i) > arr(j) Then     '   <<<<<<<<<<<<<<<<<<<
                    strTemp = arr(j)
                    arr(j) = arr(i)
                    arr(i) = strTemp
                End If
            Next j
        Next i
        
        For i = 0 To UBound(arr)  '  <<<<<<<<<<<<<<<<<<
            actual = Join(arr, ", ")
        Next i
    
    
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Thank you both, particularly Paul: this was all crystal clear and you couldn't have explained it better.

    You've really helped me a lot this evening.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •