Excel VBA -Sorting a二维数组

Question

我想用VBA按字母顺序对一个二维数组results(lcol, 4)进行排序。该数组包含4列和可变行数，基于最后一列的值。

这是我如何填充数组的代码：

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

Answer 1

这提供了一个基本的(quiksort?)对填充的数组进行升序排序，最后一列作为主键。

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

对于所有的lbound和ubound，我很抱歉，但是我不知道你的数组是从零开始还是从1开始。For i = 1 To lcol并不是决定性的。所有证据都表明你的arr是从零开始的。

Answer 2

你可以让SortedList对象来做这项工作

假设您的结果数组是从1开始的，有4列，您可以尝试以下代码(未经测试)：

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

您可以在“main”sub中调用它，如下所示：

SortArray results