Макрос для поиска ячеек в Excel с выпадающим списком и условным форматированием

В данном примере написаны исходные коды VBA-макросов для проверки ячеек на наличие выпадающих списков (или любых средств, созданных инструментом «проверка данных»). А также код макроса для проверки и получения доступа к ячейкам, которые содержат условное форматирование.

Макрос поиска ячейки с выпадающим списком

Допустим у нас имеется таблица Excel сформированная в результате экспорта журнала фактур из истории взаиморасчетов с клиентами фирмы, как показано ниже на рисунке:

Нам необходимо найти все выпадающие списки или определить каким ячейкам присвоена проверка вводимых данных, создана инструментом: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

В программе Excel по умолчанию есть встроенный инструмент для поиска ячеек с проверкой правил вводимых значений. Чтобы его использовать следует выбрать: ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек». В появившемся диалоговом окне следует отметить опцию «проверка данных» и нажать на кнопку ОК. Но как всегда более гибким решением является написание своего специального макроса. Ведь в такие случаи всегда можно усовершенствовать инструмент и дописать много других полезных функций. А этот код макроса послужит прекрасным началом программы.

Откройте редактор макросов Visual Basic (ALT+F11) и создайте новый модуль выбрав в редакторе инструмент: «Insert»-«Module». В созданный модуль введите VBA код макроса:

Sub ProvDan()
  Dim i As Long
  Dim diapaz1 As Range
  Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
On Error Resume Next
   For i = 1 To diapaz1.Count
     If IsError(diapaz1(i).Validation.Type) Then
       Else
       If diapaz2 Is Nothing Then
       Set diapaz2 = diapaz1(i)
       Else
       Set diapaz2 = Application.Union(diapaz2, diapaz1(i))
       End If
     End If
   Next
On Error GoTo 0
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодной ячейки!"
Else
  diapaz2.Select
  MsgBox "Найдено: " & diapaz2.Count & " ячеек!"
End If
End Sub


Если нужно выделить все ячейки в таблице, которые содержат проверку вводимых значений включенной инструментом «Проверка данных», тогда выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«ProvDan»-«Выполнить».

В результате выделились 14 ячеек в столбце G, для которых включена проверка данных в стиле выпадающего списка:

В данном коде мы сначала выделяем все ячейки на текущем листе с помощью инструкции:

Cells.Select

После, определяем диапазон ячеек на листе, который использует исходная таблица и с которыми будет работать наш макрос. Чтобы определить диапазон таблицы на рабочем листе Excel, мы могли бы использовать свойство UsedRange при создании экземпляра объекта Range в переменной diapaz1. Данное свойство охватывает только непустые ячейки, а это может быть даже несмежный диапазон. Но таблица может содержать пустые ячейки для, которых присвоена проверка ввода значений. Чтобы наш макрос не игнорировал пустые ячейки внутри таблицы мы определяем смежный (неразрывный) диапазон, который начинается с ячейки A1 и заканчивается последней используемой ячейкой на рабочем листе Excel.

Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

Последняя ячейка находиться наиболее отдаленно от ячейки A1 (в данном примере – это G15) и была использована на листе (это обязательное условие). При чем использована в прямом смысле, она может даже не содержать значения, но иметь измененный числовой формат, другой цвет фона, другие границы, объединение и т.п. Чтобы найти последнюю используемую ячейку на листе стандартными средствами Excel, выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек».

В появившемся окне следует выбрать опцию «последнюю ячейку». А после просто нажать ОК. Курсор клавиатуры сразу переместиться на последнюю используемую ячейку на рабочем листе Excel.

Можно даже при создании экземпляра объекта Range в переменной diapaz1 использовать диапазон целого листа. Для этого просто замените выше описанную инструкцию на:

Set diapaz1 = Selection

Так на первый взгляд даже проще, но тогда макрос будет проверять все ячейки на листе и потребует больше ресурсов. Особенно если мы при изменении этой инструкции не удалим инструкцию выделения всех ячеек на листе Excel. Таким кодом макроса, можно существенно снизить производительность работы программы Excel при его выполнении. Поэтому так делать не рекомендуется. Проверяйте ячейки только те, которые были использованы на листе. Так вы получите в десятки раз меньший диапазон и выше производительность макроса.

Далее в коде макроса перед циклом прописана инструкция для выключения обработки ошибок, выполняемых в коде.

On Error Resume Next

Но после конца цикла обработка ошибок снова включается.

On Error GoTo 0

Внутри цикла проверяться по отдельности все ячейки на наличие включенной проверки вводимых значений инструментом «Проверка данных». Если ячейка содержит проверку вводимых значений?

If IsError(diapaz1(i).Validation.Type) Then

Тогда она присоединяется к диапазону ячеек, находящихся в переменной diapaz2.

Set diapaz2 = Application.Union(diapaz2, diapaz1(i))

В конце кода выделяется несмежный диапазон переменной diapaz2, который включает в себя все выпадающие списки на текущем рабочем листе Excel. И сразу же выводиться сообщение о количестве найденных и выделенных ячеек в этом же диапазоне.

MsgBox "Найдено: " & diapaz2.Count & " ячеек!"



Макрос поиска ячейки с условным форматированием

Некоторые ячейки в исходной таблице содержат условное форматирование, а пользователю Excel необходимо их все найти и выделить. Очень часто нельзя визуально определить присвоено ли ячейке условное форматирование или нет. Чтобы найти и выделить ячейки с условным форматированием в Excel можно воспользоваться встроенным инструментом. Просто необходимо выбрать опцию в меню: «ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек».

В появившемся окне отмечаем опцию «условные форматы» и нажимаем кнопку ОК.

Но если мы хотим получить доступ к каждой выделенной ячейки и проделать с ними какие-либо операции, тогда следует воспользоваться более гибким инструментом. А конкретнее написать макрос. В данном примере мы напишем макрос, который будет автоматически выделять и считать количество ячеек с условным форматированием.

Снова откройте редактор Visual Basic (ALT+F11) и в уже созданный модуль добавьте новый код для нового макроса:

Sub ProvFormat()
  Dim i As Long
  Dim diapaz1 As Range
  Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
   For i = 1 To diapaz1.Count
     If diapaz1(i).FormatConditions.Count > 0 Then
       If diapaz2 Is Nothing Then
       Set diapaz2 = diapaz1(i)
       Else
       Set diapaz2 = Application.Union(diapaz2, diapaz1(i))
       End If
     End If
   Next
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодной ячейки!"
Else
  diapaz2.Select
  MsgBox "Найдено: " & diapaz2.Count & " ячеек!"
End If
End Sub


Теперь чтобы проверить, посчитать и выделить все ячейки для которых было применено любое условное форматирование запустите макрос: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«ProvFormat»-«Выполнить».

В результате будут выделены все соответствующие ячейки и отобразиться сообщение с информацией о их количестве:

В этом макросе уже нет необходимости отключать контроль ошибок в процессе выполнения инструкций внутри цикла.

Проверка ячеек на наличие условного форматирования выполняется с помощью свойства Cuont для объекта FormatConditions. Если данное свойство возвращает значение 0, то для текущей ячейки не применялось ни одно условное форматирование.

If diapaz1(i).FormatConditions.Count > 0 Then

Разобравшись и поняв принцип действия этих макросов, пользователь теперь может получать доступ к определенной группе чек и выполнять с ними различные действия. Просто нужно изменить макрос под свои потребности и вместе где прописана инструкция для вывода сообщения о количестве выделенных определенных групп ячеек прописать свои собственные инструкции.