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

问题描述

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

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

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

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

所以，自定义排序顺序应该是网络空间，然后是空中，陆地或海洋，然后是航空航天。然而，由于是逗号，第二类被视为三类。 Air，Land或Sea的行被排序到底部，因为Excel没有为他们找到自定义排序匹配。
有没有办法让CustomOrder使用包含嵌入式逗号的类别？

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?

我尝试在类别中放置双引号，我尝试替换分隔符逗号分号（希望Excel会接受分号而不是逗号）。

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.

推荐答案

似乎缺少申请。你可以添加

MyWorksheet.Sort.Apply

您所拥有的自定义订单按照我的样本工作。

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

编辑 OP更新问题

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

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屋！