使用自定义顺序和包含逗号的值对 Excel VBA 排序进行编码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6100944/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Code an Excel VBA sort with a custom order and a value containing commas
提问by Dean Hill
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.
在 VBA 中，Excel 允许使用 CustomOrder 参数对值进行排序，以选择排序项的顺序。不幸的是，项目序列由逗号分隔，我的排序项目之一包含逗号。例如，我想按第二列中的类别对第一列中的数据进行排序。“空中、陆地或海洋”类别包含逗号。
Data1 Aerospace Data2 Cyberspace Data3 Cyberspace Data4 Air, Land, or Sea Data5 Aerospace Data6 Air, Land, or Sea Data7 Cyberspace
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:
如果您录制 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
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?
因此，自定义排序顺序应该是“网络空间”，然后是“空中、陆地或海洋”，然后是“航空航天”。但是，由于逗号的原因，第二个类别被视为三个类别。带有“Air、Land 或 Sea”的行被排序到底部，因为 Excel 没有为它们找到自定义排序匹配。有没有办法让 CustomOrder 使用包含嵌入逗号的类别？
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.
我尝试在类别周围加上双引号，并尝试用分号替换分隔符逗号（希望 Excel 接受分号而不是逗号）。都没有工作。
回答by Steve Mallory
It seems to be missing the Apply. Can you Add
它似乎缺少Apply. 你能添加吗
MyWorksheet.Sort.Apply
The custom order you have is working as is in my sample.
您拥有的自定义订单在我的示例中正常工作。
EDITUpdated based on OP updated question
编辑根据 OP 更新问题更新
Edit the macro to the following - using an array for the OrderCustom parameter.
将宏编辑为以下内容 - 使用 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
回答by ray
OK...based on the updated description, how about a formula for the column next to what you're sorting.
好的...根据更新的描述，您正在排序的列旁边的公式如何。
So, if "Air, Land, or Sea" is in column B1, then C1 would have this:
因此，如果“空中、陆地或海洋”在 B1 列中，那么 C1 将具有以下内容：
=SUBSTITUTE(B1,",","|")
Then you could do your custom sort like so:
然后你可以像这样进行自定义排序：
MyWorksheet.Sort.SortFields.Add Key:=Range( _
"B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:= "Cyberspace,Air|Land|or Sea,Aerospace", _
DataOption:=xlSortNormal
MyWorksheet.Sort.Apply
Make sure to adjust the range appropriately.
确保适当调整范围。
