Mac端Excel VBA宏按自定义数组排序失效，按字母序排序求助

问题原因 #

你的代码尝试直接用内存数组作为Sort方法的key2参数，这是无效的——Excel的Sort方法要求排序关键字必须指向工作表中的单元格区域，而非内存中的数组。因此代码实际只按C列的字母顺序排序，自定义规则没有生效。

修正后的代码 #

Sub AutoFormat()

    ' 定义状态值的排序顺序
    Dim statusOrder(1 To 15) As String
    statusOrder(1) = "Open/Gathering proposals"
    statusOrder(2) = "Proposal Received/Construction Review"
    statusOrder(3) = "Ready To send"
    statusOrder(4) = "Sent to owner / Waiting for approval"
    statusOrder(5) = "Approval received / Add to QB/ Send deposit invoice"
    statusOrder(6) = "Waiting for deposit"
    statusOrder(7) = "Deposit received"
    statusOrder(8) = "Vendor deposit paid"
    statusOrder(9) = "PM Schedule"
    statusOrder(10) = "Work To Start"
    statusOrder(11) = "Vendor work completed"
    statusOrder(12) = "Send Final Invoice"
    statusOrder(13) = "Waiting for Final Payment"
    statusOrder(14) = "Final Payment received / sent to vendor / Closed"
    statusOrder(15) = "Canceled / Owner denied / On Hold"

    ' 将A:H单元格设置为粗体、14号字体、居中对齐、自动调整宽度（最大50）并自动换行
    With Range("A:H")
        .HorizontalAlignment = xlCenter
        .Font.Bold = True
        .Font.Size = 14
        .WrapText = True
        .EntireColumn.AutoFit
        .ColumnWidth = Application.Min(50, .ColumnWidth)
        .EntireColumn.AutoFit
        .EntireRow.AutoFit
    End With
    
    ' 根据状态值的顺序对数据进行排序
    Dim lastRow1 As Long
    lastRow1 = Cells(Rows.Count, 3).End(xlUp).Row '状态列在C列
    Dim statusPosition As Variant
    Dim helperCol As Range
    
    ' 定义辅助列（这里用I列，可根据实际调整）
    Set helperCol = Range("I1:I" & lastRow1)
    helperCol(1).Value = "SortHelper" '表头
    
    ' 填充辅助列的排序位置值
    For i = 2 To lastRow1
        statusPosition = Application.Match(CStr(Range("C" & i).Value), statusOrder, 0)
        If IsError(statusPosition) Then
            statusPosition = 0 '未匹配到的状态放在最前面
        End If
        helperCol(i).Value = statusPosition
    Next i
    
    ' 按辅助列排序
    Range("A1:I" & lastRow1).Sort _
        Key1:=helperCol, Order1:=xlAscending, _
        Header:=xlYes
    
    ' 删除辅助列
    helperCol.EntireColumn.Delete

End Sub

关键修改说明 #

1. 新增辅助列：在工作表中插入临时辅助列，存储每个状态对应的自定义排序优先级数值
2. 基于辅助列排序：将辅助列作为排序关键字，确保按自定义规则排序
3. 清理辅助列：排序完成后删除临时辅助列，不影响原数据结构
4. 移除了不必要的Select操作，直接通过With对象操作，提升代码效率和稳定性

内容的提问来源于stack exchange，提问作者Ismar Perez