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

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

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

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

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

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

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

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

    Если необходимо найти следующие ячейки, содержащие поисковую фразу, используется метод Range.FindNext.

    Параметры метода 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.

    В справке 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

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

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

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

    46 комментариев для “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
        Как в этом случае будет выглядеть запись синтаксиса?

      3. Евгений

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

      4. Дмитрий

        Здравствуйте! Помогите пожалуйста написать код … При условии, что есть диапазон со значениями дат, а нужно произвольную ячейку с датой сравнить с этим диапазоном. Если не находит совпадений то оставить эту дату в этой ячейке. Спасибо заранее

    3. Здравствуйте. Если в искомом диапазоне есть два и > искомых значения и все эти значения нужно вывести, например, в MsgBox или debug.print. С помощью цикла задача выполняется достаточно легко. А как это сделать с помощью Find, ведь он ищет только первое значение, т.е. как-то нужно включить опцию «найти все» ?

      1. Евгений

        Привет, Кирилл!
        Для указания строки используйте параметр After. Например, после 10 строки (то есть с 11-й):

    4. Здравствуйте.
      Подскажите, а откуда информация что .Find работает быстрее циклов VBA?

      1. Евгений

        Добрый день, Антон!
        Это была ошибка. Тест на моем компьютере показал, что цикл For Each… Next находит значение в последней ячейке первого столбца за 1,53 сек., а метод Find – за 2,20 сек. Спасибо за обратную связь!

    5. Здравствуйте, Евгений. Имеется таблица. В левой колонке — цифры(отфильтрованы в порядке возрастания). Задача-по значению первой колонке найти остальные параметры. Точного совпадения нет. Написать поиск в VBA.(что-то типа ИНДЕКС(ПОИСКПОЗ) или ВПР в Excel). Поиск с помощью FIND-ничего не дает. Требуется только точное совпадение. Поиск в цикле-та же песня. Подскажите пожалуйста как решить этот вопрос. Cпасибо.

    6. Помогите плизз. Есть 3 столбца. Надо оставить «Дебитор доставки» остальные удалить. Создаю 2 запроса одинаковых. С 3 столбцом все нормально а вот 1-2 удаляются вместе. И как задать в одном запросе удаление дебитора и юр. адрес клиента
      (Set A = .Find(What:=("Юр. адрес клиента","Дебитор"), LookIn:=xlValues, lookat:=xlPart)) — так не работает.
      Дебитор
      Дебитор доставки
      Юр. адрес клиента

      1. Сделал. Сначала переименовал, а потом удалил.

    7. Добрый день. Очень интересная статья
      Подскажите пожалуйста, как добиться следующего цикла.
      Например: надо найти ячейки со словом «ЖКХ» и «квартплата» в столбце А и проставить это наименование в столбец В.
      А1 оплата ЖКХ
      А2 квартплата за Иванова
      А3 квартплата за ЖКХ

      Соответственно в столбце В должно быть
      В1 ЖКХ
      В2 квартплата
      В3 квартплата ЖКХ (или хотябы по первому поисковому слову ЖКХ)
      Заранее спасибо.

      1. Евгений

        Здравствуйте, Сергей!
        Для поиска совпадений в количестве более одного вместе с методом Range.Find для продолжения поиска используется метод Range.FindNext:

        В этом примере слово «ЖКХ» заменит слово «квартплата» в ячейке справа, если они оба будут найдены в одной ячейке слева.

        1. Доброго времени суток, Евгений! Подскажите, пожалуйста, есть файл в нем надо найти скрытое слово, то есть мы не знаем что ищем. Как в таком случае искользовать VBA Find function? Спасибо!

          1. Евгений

            Добрый день, Ainur!
            Если искомое слово (или его часть) неизвестно, то найти его с помощью метода Find невозможно.

    8. Сбитый Лётчик Небосводов

      Здравствуйте!
      Спасибо за статью.
      Может, кто знает: как найти ячейку по значению, если в ней формула? И в VBA, и в таблицах любопытно.

      1. Евгений

        Добрый день!
        Значения ищутся по умолчанию, не зависимо от того, есть ли в ячейке формула или нет. А вот чтобы найти формулу, необходимо указать явно, что ищется формула:

    9. Сбитый Лётчик Небосводов

      Спасибо.
      Что нужно указать, чтобы найти ячейку, в которой формула, но ищется которая по значению?

      1. Евгений

        Нужно проверить наличие формулы:

        1. Сбитый Лётчик Небосводов

          Т.е. .Find в этом случае не работает ∎
          Ждал такого ответа.
          Спасибо и примите восхищение поддержкой.

    10. Дмитрий

      Евгений, здравствуйте! Огромное спасибо за статью. Подскажите пожалуйста, подойдет ли Find Range для решения следующей задачи:
      Имеется некоторая база с неким продуктом, у каждого продукта свой каталожный номер, каталожные номера записаны в столбец, а в строках содержится остальная информация по товару (цена, производитель, срок гарантии, импортер и т.д.), можно ли использовать Find для автоматического заполнения строки после ввода каталожного номера в ячейку? Если совсем обывательским языком говорить, то программа должна делать следующее: введенное в ячейку значение сравнивает со всеми значениями ячеек в этом столбце, которые расположены выше изменяемой ячейки. Если каталожный номер (читай значение) совпадает с уже встречающимся выше каталожным номером, то программа автоматически заполняет строку значениями, которые соответствуют данному кат. номеру, в ином случае, когда совпадающего значения не найдено — оставляет строку пустой. Спасибо за ваше время и еще раз за вашу статью!

      1. Евгений

        Здравствуйте, Дмитрий!
        Вы можете использовать метод Range.Find для решения своей задачи. А если столбец с каталожными номерами является первым в таблице, вы также можете использовать метод WorksheetFunction.VLookup.

    11. Добрый день.
      Подскажите, как присвоить из таблицы нужное мне значение (Цена без НДС) ?
      Прошу прощения, на работе нет программиста, приходится самому учиться(
      Пример ниже не правильный.

      Как правильно?

      1. Евгений

        Здравствуйте, Виктор!
        Вместо строки "Цена без НДС" должен быть указан номер столбца или его буквенное обозначение, например, "D".

    12. Подскажите, пожалуйста, как найти число с форматированием группы знаков (например 15 678,99), само число 15678,99.
      Если искать «15678,99», то ничего не находится, только если «15 678,99»?

      1. Евгений

        Добрый день, Сергей!
        Скорее всего, в вашем диапазоне к числам применен текстовый формат. Замените текстовый формат ячеек на числовой с разделителем групп разрядов, если он нужен. Если в коде VBA вы будете указывать искомое число вручную, заменяйте десятичную запятую на точку.

        1. Спасибо за ответ.
          Формат числовой. Если на странице в любую ячейку вписать 1567,89 потом применить форматирование ячейки как число с разделителем разрядов и двумя знаками после запятой, будет выглядеть как 1 567,89
          Теперь, если искать значение (не формулу!) 1567 то ничего не найдется, а если «1 567», тогда выделяется наша ячейка(

          1. Евгений

            Сергей, у меня ячейка выделяется. Сделал, как вы написали: вписал в ячейку 1567,89, потом применил форматирование — как число с разделителем разрядов и двумя знаками после запятой, получилось — 1 567,89. Проверьте с моим кодом:

            1. Верно, по-умолчанию ищется с параметром LookIn:=xlFormulas, а мне нужно чтобы было LookIn:=xlValues, т.к. вместо числа в ячейке м.б. ссылка (=D3), в которой будет число 1567,89
              Тогда код будет такой и ничего не найдется

              1. Евгений

                Сергей, по умолчанию LookIn:=xlValues, но когда мы ищем число 1567, оно не равно числу 1567.89, поэтому метод Find его не находит. По частичному совпадению мы можем искать только строки. Используя метод Find, мы не можем указать, чтобы числовые значения ячеек воспринимались как текст. Но мы можем преобразовать числа в строки, проходя диапазон циклом:

          2. Владислав

            Добрый день, не могли бы вы подсказать пожалуйста, как найти и очистить ячейки, в которых находится только один символ?
            У меня данный символ является 0, но в других ячейках он присутствует и тексте, и удаляться ни в коем случае не должен, заранее благодарю

            1. Евгений

              Добрый день!

    13. Спасибо за ответ, но мая задача немного в другом: есть столбец чисел на листе1 a1=1500000, a2=1800000… отформатированы как числа с разделителем и 2 цифрами после запятой и эти числа надо найти последовательно на другом листе2, на котором они представлены формулами как ссылки на лист1 (c1 — «=Лист1!A1», c2 — «=Лист1!A2″…) так же отформатированы как числа с разделителем и 2 цифрами после запятой.
      Я делаю так:

      И у меня не получается их найти

    14. Сбитый Лётчик Небосводов

      Здравствуйте!
      Можно добавить, что Find выдаёт ошибку, если на его пути встретятся объединённые ячейки.

    15. Подскажите, а как найти ячейки и очистить, если в ячейке значение, определенного формата? Нужно найти ячейки от 7-ми до 11-ти значных.
      Тоесть от 000000 до 99999999999…
      указать формат «ddddddd» ?

      1. Евгений

        Добрый день, Юрий!
        Используйте такой вариант:

        1. блин, точно, я забыл про IsNumeric
          Спасибо.

          Хотел уже пойти путем чуть другим, указав условие через Select Case:

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

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