如何在VBA中对数组中的每一行分别进行排序? [英] How to sort every row individually in an array in VBA?

问题描述

我用以下代码构造了一个数组:

I constructed an array with the following code:

For i = 1 To Vehiclenumber
For j = 1 To Vendornumber
Worksheets("Vendor").Cells(i + 8, j + 4) = Worksheets("Shipment").Cells(i 
+ 13, j + 2).Value * Worksheets("Vendor").Cells(j + 1, 6)
Next j
Next i

我有以下阵列(20辆汽车-5个供应商):

And I had the following array (20 vehicles-5 vendor):

我想按降序对每行(每辆车)的值进行排序，但不扩展选择范围.所以我想将每一行作为一个数组并对它排序.我什至不确定是否可以.

I want to sort the values for every row (for every vehicle) in descending order but without expanding the selection. So I want to take every row as an array and sort it. I am not even sure if it is possible.

推荐答案

将以下代码添加到模块中...

Add the below code to a module ...

Public Sub SortColumnsDescending()
    Dim rngData As Range, lngRow As Long, lngCol As Long, arrData() As Double
    Dim lngIndex As Long, i As Long
    Dim x As Long, lngMin As Long, lngMax As Long, strTemp As String

    Set rngData = Selection

    With rngData
        For lngRow = 1 To rngData.Rows.Count
            lngIndex = -1

            For lngCol = 1 To rngData.Columns.Count
                lngIndex = lngIndex + 1

                ReDim Preserve arrData(lngIndex)
                arrData(lngIndex) = rngData.Cells(lngRow, lngCol)
            Next

            lngMin = LBound(arrData)
            lngMax = UBound(arrData)

            For i = lngMin To lngMax - 1
                For x = i + 1 To lngMax
                    If arrData(i) > arrData(x) Then
                        strTemp = arrData(i)
                        arrData(i) = arrData(x)
                        arrData(x) = strTemp
                    End If
                Next
            Next

            lngCol = 1

            For i = UBound(arrData) To 0 Step -1
                rngData.Cells(lngRow, lngCol) = arrData(i)
                lngCol = lngCol + 1
            Next
        Next
    End With
End Sub

...，然后选择不包含标题的数据(如下所示)并运行宏.屏幕截图显示了排序后的数据.

... and then select your data without headers (as shown below) and run the macro. The screen shot shows the data AFTER it's sorted.

我希望对您有用.

这篇关于如何在VBA中对数组中的每一行分别进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！