Закодируйте сортировку Excel VBA с пользовательским порядком и значением, содержащим запятые

ProgramBox

Закодируйте сортировку Excel VBA с пользовательским порядком и значением, содержащим запятые

Post author:admin
Запись опубликована:22 апреля, 2023
Post category:Вопросы по программированию

#sorting #vba #excel

#сортировка #vba #excel

Вопрос:

В VBA Excel позволяет сортировать значения с помощью параметра CustomOrder для выбора последовательности упорядочивания элементов. К сожалению, последовательность элементов разделена запятыми, и один из моих элементов сортировки содержит запятые. Например, я хочу отсортировать данные в первом столбце по категориям во втором столбце. Категория «Воздух, суша или море» содержит запятые.

Data1 Aerospace
Киберпространство Data2
Киберпространство Data3
Данные 4 Воздушные, наземные или морские
Data5 Aerospace
Данные 6 Воздушные, наземные или морские
Киберпространство 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

Итак, пользовательский порядок сортировки должен быть «киберпространство», затем «Воздух, суша или море», затем «Аэрокосмическая отрасль». Однако вторая категория рассматривается как три категории из-за запятых. Строки с «Воздух, Суша или море» сортируются снизу, потому что Excel не находит для них соответствия пользовательской сортировке.
Есть ли способ заставить CustomOrder работать с категорией, содержащей встроенные запятые?

Я попытался заключить категорию в двойные кавычки и заменить разделительные запятые точкой с запятой (в надежде, что Excel примет точку с запятой вместо запятой). Ни то, ни другое не сработало.

Ответ №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

1. Мой первоначальный пример был не очень понятным. Пожалуйста, прочитайте обновленный вопрос.

2. @Dean Hill обновил мой ответ.

3. Я следовал этому решению, которое работает, за исключением того, что оно приводит к сбою Excel 2013 при попытке сохранения после запуска макроса. Решение заключается в добавлении строки: ActiveSheet.Sort.Sortfields.Clear Перед строкой: Application.DeleteCustomList Application.CustomListCount Подробности здесь: answers.microsoft.com ссылка

4. @Slab, спасибо за это, мой Excel по-прежнему выходит из строя после применения вашего решения, есть какие-либо другие обходные пути?

5. @excelguy Извините, я не уверен. Возможно, последний комментарий к моей ссылке на Microsoft может помочь? «вы должны очистить поля сортировки на каждом листе, где это было использовано»

Ответ №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

Убедитесь в том, что диапазон отрегулирован соответствующим образом.

Ответ №3:

Использование дополнительных кавычек также должно работать нормально

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