Вопрос:
Мне нужно создать вложенную структуру фильтра в Excel с помощью VBA. Подходом является создание двух списков проверки данных, содержащих индексы сортировки. У меня есть три возможных индексации, которые являются lastName, FirstName и Company. Поскольку в функции сортировки не допускается передача указателей за пределы диапазона сортировки. Я строю эти 6 возможных комбинаций индексов, например ниже.
Sub Macro_Sort1() Sheets(«Reports»).Range(«F1:CT10000»).Sort Key1:=Sheets(«Reports»).Range(«I1»), Order1:=xlAscending, Key2:=Sheets(«Reports»).Range(«J1»), Order1:=xlAscending, Header:=xlYes End Sub
И теперь мне нужно назначить этот макрос этим двум спискам проверки данных, например
If Target.Address = «$B$1» and «$B$2» Then Select Case Target.Value2 Case «Last» and «First» Call Macro_Sort1 Case «Last» and «Company» Call Macro_Sort2 Case «First» and «Last» Call Macro_Sort3 Case «First» and «Company» Call Macro_Sort4 Case «Company» and «Last» Call Macro_Sort5 Case «Company» and «First» Call Macro_Sort6 End Select End if End Sub
Однако, очевидно, это говорит мне, что есть ошибка с предложением Target.Address в отношении THEN.
Пожалуйста, помогите мне выяснить, есть ли другой подход для достижения этого.
Заранее спасибо!
Обновить:
Как я уже упоминал, у меня есть два фильтра, и первый из них можно использовать независимо, у него есть макрос, назначенный ему, как показано ниже:
Sub Macro_Sort1() Sheets(«Reports»).Range(«F1:CT10000»).Sort Key1:=Sheets(«Reports»).Range(«I1»), Header:=xlYes, Order1:=xlAscending End Sub
Поэтому перед кодом для обоих фильтров будет такой код:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = «$B$17» Then Select Case Target.Value2 Case «Last» Call Macro_Sort1 Case «First» Call Macro_Sort2 Case «Company» Call Macro_Sort3 End Select
Основываясь на некоторых рекомендациях, я добавил эту часть после предыдущей.
ElseIf Target.Address(0, 0) = «$B$17» Or Target.Address(0, 0) = «$B$18» Then SortUsingDataValidation Range(«B17»).Value, Range(«B18»).Value End If End Sub Private Sub SortUsingDataValidation(sFirst As String, sSecond As String) Dim oDict As Object Set oDict = CreateObject(«Scripting.Dictionary») With ThisWorknook.shhets(«Reports») Set oDict(«Last») = .Range(«I1») Set oDict(«First») = .Range(«J1») Set oDict(«Company») = .Range(«K1») If oDict.exists(sFirst) And oDict.exists(sSecond) Then .Range(«F1:CT10000»).Sort Key1:=oDict(sFirst), Order1:=xlAscending, key2:=oDict(sSecond), Order1:=xlAscending, Header:=xlYes End If End With End Sub
Хотя это действительно хороший подход, он не запускается. Нет ошибок или всплывают окна сообщений. Поэтому, пожалуйста, проверьте, является ли код первого фильтра влияющим на код двойного фильтра.
Большое спасибо!
Лучший ответ:
Отредактировано:
-
добавлен метод Application.Transpose() в диапазон Target поскольку B1:B2 является одностолбцовым “вертикальным” диапазоном, тогда как Join() ожидает массив, который соответствует однострочному “горизонтальному” диапазону
-
добавлены некоторые “ароматы” кода для работы в разных средах
Изменение подхода
If Target.Address = «$B$1:$B$2» Then Dim strng As String Dim pos as Long strng = «1LastFirst,2LastCompany,3FirstLast,4FirstCompany,5CompanyLast,6CompanyLast» pos = Instr(strng,Join (Application.Transpose(Target),»»)) If pos > 0 then Application.Run «Macro_Sort» & Mid(strng,pos-1,1) End If
Наконец, некоторые рекомендации по охране окружающей среды:
Если вы запускаете этот код внутри обработчика событий Worksheet, тогда
-
If Target.Address = «$B$1:$B$2» может быть True только в обработчике Worksheet_SelectionChange, тогда как другие релевантные обработчики, имеющие отношение к параметру Target range, будут обнаруживать только одноячеечный диапазон
Например, если вам нужно запустить его внутри обработчика событий Worksheet_Change вам нужно будет изменить проверку на некоторые:
If Not Intersect(Target, Range («B1:B2»)) Is Nothing Then
Что можно было бы сделать и во многих других случаях
-
Подписки, вызываемые методом Application.Run должны быть помещены в любой модуль, но не в Рабочий лист
Ответ №1
Несколько вещей;
Адрес будет “B1: B2”
И чтобы выполнить несколько тестов с помощью Select Case, мы хотим найти True и форматировать его как IF/ElseIF:
If target.Address(0, 0) = «B1:B2» Then Select Case True Case Range(«B1») = «Last» And Range(«B2») = «First» Call Macro_Sort1 Case Range(«B1») = «Last» And Range(«B2») = «Company» Call Macro_Sort2 Case Range(«B1») = «First» And Range(«B2») = «Last» Call Macro_Sort3 Case Range(«B1») = «First» And Range(«B2») = «Company» Call Macro_Sort4 Case Range(«B1») = «Company» And Range(«B2») = «Last» Call Macro_Sort5 Case Range(«B1») = «Company» And Range(«B2») = «First» Call Macro_Sort6 End Select End If Ответ №2
If Target.Address = «$B$1» And «$B$2» Then
Это синтаксическая ошибка, потому что If {boolean-expression} Then требует, ну, булево выражение – и вы путали VBA с частью AND And {string-literal} этого выражения.
Вы можете построить булевое выражение с ключевым словом And, как это:
{boolean-expression} And {boolean-expression}
Другими словами, вы могли бы сделать:
If Target.Address = «$B$1» And Target.Address = «$B$2» Then
Но, как вы подозреваете, эта логика всегда должна возвращать False, потому что если Target.Address равен «$B$1» то он также не может быть равен «$B$2».
Остальное решение имеет ответ Скотта.
Ответ №3
Я собираюсь добавить другой подход, потому что, как правило, неплохо иметь 6 разных макросов, чтобы сделать по существу одно и то же. Он будет работать, но это больше кода для поддержки, что затрудняет будущие изменения.
В приведенном ниже коде значения полей проверки передаются в функцию, которая (1) связывает каждое значение с соответствующим диапазоном, (2) сортирует на основе соответствующих диапазонов.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(0, 0) = «B1» Or Target.Address(0, 0) = «B2» Then SortUsingDataValidation Range(«B1»).Value, Range(«B2»).Value End If End Sub Private Sub SortUsingDataValidation(sFirst As String, sSecond As String) Dim oDict As Object ‘Create a dictionary (or any collection) to hold references from a data validation ‘String to its corresponding cell reference Set oDict = CreateObject(«Scripting.Dictionary») With ThisWorkbook.Sheets(«Reports») Set oDict(«First») = .Range(«I1») Set oDict(«Last») = .Range(«J1») Set oDict(«Company») = .Range(«K1») If oDict.exists(sFirst) And oDict.exists(sSecond) Then .Range(«F1:CT10000»).Sort Key1:=oDict(sFirst), Order1:=xlAscending, Key2:=oDict(sSecond), Order1:=xlAscending, Header:=xlYes End If End With End Sub
