VBA Excel. Метод Find объекта Range

Метод Find объекта Range для поиска ячейки по ее данным в VBA Excel. Синтаксис и компоненты. Знаки подстановки для поисковой фразы. Простые примеры.

Предназначение и синтаксис метода Range.Find

Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.

Синтаксис метода Range.Find

Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.

В скобках перечислены параметры метода, среди них только What является обязательным.

Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.

Параметры метода Range.Find

Наименование Описание
Обязательный параметр
What Данные для поиска, которые могут быть представлены строкой или другим типом данных Excel. Тип данных параметра – Variant.
Необязательные параметры
After Ячейка, после которой следует начать поиск.
LookIn Уточняет область поиска. Список констант xlFindLookIn:
  • xlValues (-4163) – значения;
  • xlComments (-4144) – примечания*;
  • xlNotes (-4144) – примечания*;
  • [xlFormulas (-4123) – формулы]**.
LookAt Поиск частичного или полного совпадения. Список констант xlLookAt:
  • xlWhole (1) – полное совпадение;
  • xlPart (2) – частичное совпадение.
SearchOrder Определяет способ поиска. Список констант xlSearchOrder:
  • xlByRows (1) – поиск по строкам;
  • xlByColumns (2) – поиск по столбцам.
SearchDirection Определяет направление поиска. Список констант xlSearchDirection:
  • xlNext (1) – поиск вперед;
  • xlPrevious (2) – поиск назад.
MatchCase Определяет учет регистра:
  • False (0) – поиск без учета регистра (по умолчанию);
  • True (1) – поиск с учетом регистра.
MatchByte Условия поиска при использовании двухбайтовых кодировок:
  • False (0) – двухбайтовый символ может соответствовать однобайтовому символу;
  • True (1) – двухбайтовый символ должен соответствовать только двухбайтовому символу.
SearchFormat Формат поиска – используется вместе со свойством Application.FindFormat.

* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes.
** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.

В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.

Знаки подстановки для поисковой фразы

Условные знаки в шаблоне поисковой фразы:

  • ? – знак вопроса обозначает любой отдельный символ;
  • * – звездочка обозначает любое количество любых символов, в том числе ноль символов;
  • ~ – тильда ставится перед ?, * и ~, чтобы они обозначали сами себя (например, чтобы тильда в шаблоне обозначала сама себя, записать ее нужно дважды: ~~).

Простые примеры

При использовании метода Range.Find в VBA Excel необходимо учитывать следующие нюансы:

  1. Так как этот метод возвращает объект Range (в виде одной ячейки), присвоить его можно только объектной переменной, объявленной как Variant, Object или Range, при помощи оператора Set.
  2. Если поисковая фраза в заданном диапазоне найдена не будет, метод Range.Find возвратит значение Nothing. Обращение к свойствам несуществующей ячейки будет генерировать ошибки. Поэтому, перед использованием результатов поиска, необходимо проверить объектную переменную на содержание в ней значения Nothing.

В примерах используются переменные:

  • myPhrase – переменная для записи поисковой фразы;
  • myCell – переменная, которой присваивается первая найденная ячейка, содержащая поисковую фразу, или значение Nothing, если поисковая фраза не найдена.

Пример 1

В этом примере мы присваиваем переменной myPhrase значение для поиска – "стакан". Затем проводим поиск этой фразы в диапазоне "A1:L30" с присвоением результата поиска переменной myCell. Далее проверяем переменную myCell, не содержит ли она значение Nothing, и выводим соответствующие сообщения.

Ознакомьтесь с работой кода VBA в случаях, когда в диапазоне "A1:L30" есть ячейка со строкой, содержащей подстроку "стакан", и когда такой ячейки нет.

Пример 2

Теперь посмотрим, как метод Range.Find отреагирует на поиск числа. В качестве диапазона поиска будем использовать первую строку активного листа Excel.

Несмотря на то, что мы присвоили переменной числовое значение, метод Range.Find найдет ячейку со значением и 526,15, и 129526,15, и 526,15254. То есть, как и в предыдущем примере, поиск идет по подстроке.

Чтобы найти ячейку с точным соответствием значения поисковой фразе, используйте константу xlWhole параметра LookAt:

Аналогично используются и другие необязательные параметры. Количество «лишних» запятых перед необязательным параметром должно соответствовать количеству пропущенных компонентов, предусмотренных синтаксисом метода Range.Find.

Пример 3

Метод Find объекта Range работает намного быстрее циклов VBA, поэтому именно его следует использовать для поиска необходимой информации в большой таблице.

Допустим, у нас есть многострочная база данных в Excel. В первой колонке находятся даты. Нам необходимо создать отчет за какой-то период. Найти номер начальной строки для обработки можно с помощью следующего кода:

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

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

8 комментариев для “VBA Excel. Метод Find объекта Range”

  1. 21/08/20
    как задать поиск точного, но регистронезависимого значения ?
    напр. дано
    Иванов
    диван
    Иван
    ивановский район

    myPhrase = “иван”
    машина должна найти 4-ое слово “Иван”

    1. Евгений

      Привет, 0mega!

      • LookAt:=1 – поиск полного совпадения искомой фразы со значением ячейки. Указывать обязательно, так как по умолчанию – поиск по частичному совпадению.
      • MatchCase:=0 – поиск без учета регистра. Указывать не обязательно, так как используется по умолчанию.
    1. Евгений

      Заливка найденной ячейки зеленым цветом, просмотр ее адреса и номера строки:

  2. А как задать поиск точного, но регистрозависимого значения, с поиском совпадения внутри, но с учетом последних двух символов, в данном примере это _5?
    напр. дано
    Иванов_123
    диван_123
    Иван_2_5
    Иван_3
    Иван_321_3
    ивановский район_521

    myPhrase = “Иван, где в конце _5”
    машина должна найти 3-е слово “Иван_2_5”

    1. Привет, Иван! Так как в начале вопроса написано “А как задать поиск точного, но регистрозависимого значения…”, я в предпоследней строке (myPhrase = “иван, где в конце _5”) исправил строчную “и” на заглавную.

      • * – звездочка обозначает любое количество любых символов.
      • LookAt:=2 – поиск частичного совпадения искомой фразы со значением ячейки. Указывать не обязательно, так как используется по умолчанию.
      • MatchCase:=1 – поиск с учетом регистра. Указывать обязательно, так как по умолчанию – поиск без учета регистра.
    2. Привет Евгений! Благодарю! Пусть остается регистронезависимый.
      И хотелось бы увидеть еще один пример, где слово Иван – есть переменная, но с окончанием “*_5”
      Предположим переменная – Dim myIvan As Range
      Как в этом случае будет выглядеть запись синтаксиса?

      myPhrase = “Иван*_5”
      Set myCell = Range(“A1:A6”).Find(myPhrase, LookAt:=2, MatchCase:=1)

    3. Если оставить регистронезависимый поиск по частичному совпадению, тогда параметры поиска можно не указывать:

Добавить комментарий

Ваш комментарий будет опубликован после прохождения обязательной модерации. Исходящие ссылки не допускаются. Время модерации составит от нескольких минут до нескольких часов в зависимости от времени суток и занятости модератора. При добавлении в комментарий кода VBA Excel, вставьте перед его началом тег <pre> и по окончании кода </pre>.