使用自定义顺序和包含逗号的值编写 Excel VBA 排序代码

Question

在 VBA 中，Excel 允许使用 CustomOrder 参数对值进行排序，以选择排序的序列项。不幸的是，项目序列是用逗号分隔的，并且我的排序项目之一包含逗号。例如，我想按第二列中的类别对第一列中的数据进行排序。 “空中、陆地或海上”类别包含逗号。

Data1 航空航天
Data2网络空间
Data3网络空间
Data4 空中、陆地或海上
Data5 航空航天
Data6 空中、陆地或海上
Data7 网络空间

如果您录制 VBA 宏，则创建的代码如下所示：

MyWorksheet.Sort.SortFields.Add Key:=Range( _
    "B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, _
    CustomOrder:= "Cyberspace,Air,Land,or Sea,Aerospace", _
    DataOption:=xlSortNormal  
MyWorksheet.Sort.Apply

因此，自定义排序顺序应为“网络空间”，然后是“空中、陆地或海洋”，然后是“航空航天”。然而，由于逗号的原因，第二个类别被视为三个类别。包含“Air、Land 或 Sea”的行会排序到底部，因为 Excel 找不到它们的自定义排序匹配项。 有没有办法让 CustomOrder 处理包含嵌入逗号的类别？

我尝试在类别周围加上双引号，并尝试用分号替换分隔符逗号（希望 Excel 能够接受分号而不是逗号）。两者都不起作用。

Answer 1

似乎缺少Apply。您可以添加

MyWorksheet.Sort.Apply

您的自定义订单正在按我的示例中的方式工作吗？

编辑根据OP更新的问题进行更新

将宏编辑为以下内容 - 使用 OrderCustom 参数的数组。

Dim oWorksheet As Worksheet
Set oWorksheet = ActiveWorkbook.Worksheets("Sheet1")
Dim oRangeSort As Range
Dim oRangeKey As Range

' one range that includes all colums do sort
Set oRangeSort = oWorksheet.Range("A1:B9")
' start of column with keys to sort
Set oRangeKey = oWorksheet.Range("B1")

' custom sort order
Dim sCustomList(1 To 3) As String
sCustomList(1) = "Cyberspace"
sCustomList(2) = "Aerospace"
sCustomList(3) = "Air, Land, or Sea"

Application.AddCustomList ListArray:=sCustomList
' use this if you want a list on the spreadsheet to sort by
' Application.AddCustomList ListArray:=Range("D1:D3")

oWorksheet.Sort.SortFields.Clear
oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

' clean up
Application.DeleteCustomList Application.CustomListCount
Set oWorksheet = Nothing

Answer 2

好的...根据更新的描述，您要排序的内容旁边的列的公式怎么样。

因此，如果“空中、陆地或海洋”位于 B1 列中，则 C1 中将显示以下内容：

=SUBSTITUTE(B1,",","|")

然后您可以像这样进行自定义排序：

MyWorksheet.Sort.SortFields.Add Key:=Range( _

        "B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        CustomOrder:= "Cyberspace,Air|Land|or Sea,Aerospace", _
        DataOption:=xlSortNormal  
    MyWorksheet.Sort.Apply

确保适当调整范围。

Answer 3

使用额外的引号也应该可以正常工作

CustomOrder:="""Cyberspace,Air"",""Land"",""or Sea,Aerospace"""