Excel VBA -Sorting a 2-dimensional array

Question

I want to alphabetically sort a 2-dimensional array results(lcol, 4) with VBA. This array contains 4 columns and variable number of rows, based on the values of the last column.

This is the code of how I populated the array :

ReDim results(lcol, 4)

For i = 1 To lcol
    results(i, 1) = ThisWorkbook.Sheets(2).Range("B1").Offset(, i - 1).Value
    results(i, 2) = "0"
    results(i, 3) = ThisWorkbook.Sheets(3).Range("C2").Offset(i - 1, 0).Value
Next i



    For Each of In ThisWorkbook.Sheets(1).Range("A1:C" & lrow2)
        Set modele = of.Offset(, 1)
        Set qte = of.Offset(, 2)

        For Each modele2 In ThisWorkbook.Sheets(2).Range("A2:A481")
            If modele2.Value = modele.Value Then
                For i = 1 To lcol 'à modifier
                    results(i, 2) = results(i, 2) + qte.Value * modele2.Offset(, i).Value
                    If results(i, 2) <= results(i, 3) Then
                        results(i, 4) = "OK"
                    Else
                        results(i, 4) = "Rupture"
                    End If

                Next i
                Exit For
            End If
        Next modele2
    Next of

could you supply some data and expected outcome? You can use a [table generator](https://www.tablesgenerator.com/markdown_tables) to format the data as a table and insert that between code tags. — QHarr, Apr 15 '18 at 19:23
Hello please find int the following answer the tables requested. thank you for your help — Mohammed Zegui, Apr 15 '18 at 19:30
https://stackoverflow.com/questions/4873182/sorting-a-multidimensionnal-array-in-vba — Tim Williams, Apr 15 '18 at 19:31

score 0 · Answer 1 · 2018-04-15T20:14:50.817

This provides a basic (quiksort?) ascending sort on your populated array with the last column as the primary key.

dim i as long, j as long, tmp as variant

redim tmp(lbound(results, 1) to lbound(results, 1), lbound(results, 2) to ubound(results, 2))

for i = lbound(results, 1) to ubound(results, 1) - 1
    if results(i, ubound(results, 2)) > results(i+1, ubound(results, 2)) or _
     results(i, ubound(results, 2)) = vbnullstring then
       for j = lbound(results, 2) to ubound(results, 2)
           tmp(lbound(results, 1), j) = results(i, j)
       next j
       for j = lbound(results, 2) to ubound(results, 2)
           results(i, j) = results(i+1, j)
       next j
       for j = lbound(results, 2) to ubound(results, 2)
           results(i+1, j) = tmp(lbound(results, 1), j) 
       next j
    end if
next i

Sorry for all the lbound and ubound but I had no idea if your array was zero-based of 1-based. The For i = 1 To lcol was not definitive. All evidence points to your arr being zero-based.

score 0 · Answer 2 · answered Apr 15 '18 at 20:35

You could have SortedList object do the work

Assuming your results array is 1-based and with 4 columns, you could try the following code (UNTESTED):

Sub SortArray(results As Variant)
    Dim i As Long, j As Long

    With CreateObject("System.Collections.SortedList")
        For i = 1 to UBound(results)
            .Add results(i,4), Application.Index(result,i,0)
        Next
        For i = 1 To .Count
            For j = 1 To 4
                results(i, j) = .GetByIndex(i)(j)
            Next
        Next
    End With
End Sub

Which you would call in your “main” sub as follows:

SortArray results

Excel VBA -Sorting a 2-dimensional array

2 Answers2