使用自定义顺序和包含逗号的值对 Excel VBA 排序进行编码 [英] Code an Excel VBA sort with a custom order and a value containing commas

问题描述

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

<块引用>

Data1 航空航天数据2网络空间数据3网络空间Data4 空中、陆地或海上数据5航空航天Data6 空中、陆地或海上数据7网络空间

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

MyWorksheet.Sort.SortFields.Add Key:=Range( _"B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, _CustomOrder:= "网络空间、空中、陆地或海洋、航空航天", _DataOption:=xlSortNormalMyWorksheet.Sort.Apply

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

我尝试在类别周围加上双引号，并尝试用分号替换分隔符逗号(希望 Excel 接受分号而不是逗号).都没有用.

解决方案

似乎缺少Apply.可以加吗

MyWorksheet.Sort.Apply

您的自定义订单在我的示例中正常工作.

EDIT 根据 OP 更新问题更新

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

将工作表变暗为工作表设置 oWorksheet = ActiveWorkbook.Worksheets("Sheet1")Dim oRangeSort As RangeDim oRangeKey 作为范围' 一个包含所有列的范围进行排序Set oRangeSort = oWorksheet.Range("A1:B9")' 带有要排序的键的列的开头设置 oRangeKey = oWorksheet.Range("B1")' 自定义排序Dim sCustomList(1 到 3) 作为字符串sCustomList(1) = "网络空间"sCustomList(2) = "航空航天"sCustomList(3) = "空中、陆地或海上"Application.AddCustomList ListArray:=sCustomList' 如果您希望电子表格上的列表作为排序依据，请使用此选项' Application.AddCustomList ListArray:=Range("D1:D3")oWorksheet.Sort.SortFields.ClearoRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _方向:=xlTopToBottom，DataOption1:=xlSortNormal' 清理Application.DeleteCustomList Application.CustomListCount设置 oWorksheet = 无

In VBA, Excel allows sorting values using the CustomOrder parameter to choose the sequence items are ordered. Unfortunately, the sequence of items is delimited by commas and one of my sort items contains commas. For example, I want to sort the data in the first column by the categories in the second column. The "Air, Land, or Sea" category contains commas.

Data1     Aerospace
Data2     Cyberspace
Data3     Cyberspace
Data4     Air, Land, or Sea
Data5     Aerospace
Data6     Air, Land, or Sea
Data7     Cyberspace

If you record a VBA macro, the code created looks like this:

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

So, the custom sort order should be "Cyberspace" then "Air, Land, or Sea", then "Aerospace". However, the second category is treated as three categories because of the commas. The rows with "Air, Land, or Sea" get sorted to the bottom because Excel doesn't find a custom sort match for them. Is there a way to get CustomOrder to work with a category that contains embedded commas?

I tried putting double quotes around the category and I tried replacing the delimiter commas with semicolons (in the hope Excel would accept a semicolon instead of a comma). Neither worked.

解决方案

It seems to be missing the Apply. Can you Add

MyWorksheet.Sort.Apply

The custom order you have is working as is in my sample.

EDIT Updated based on OP updated question

Edit the macro to the following - using an array for the OrderCustom parameter.

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

这篇关于使用自定义顺序和包含逗号的值对 Excel VBA 排序进行编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！