Метод Find объекта Range для поиска ячейки по ее данным в VBA Excel. Синтаксис и компоненты. Знаки подстановки для поисковой фразы. Простые примеры.
Предназначение и синтаксис метода Range.Find
Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.
Синтаксис метода Range.Find
|
1 |
Expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat) |
Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.
В скобках перечислены параметры метода, среди них только What является обязательным.
Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.
Если необходимо найти следующие ячейки, содержащие поисковую фразу, используется метод Range.FindNext.
Параметры метода Range.Find
| Наименование | Описание |
|---|---|
| Обязательный параметр | |
| What | Данные для поиска, которые могут быть представлены строкой или другим типом данных Excel. Тип данных параметра — Variant. |
| Необязательные параметры | |
| After | Ячейка, после которой следует начать поиск. |
| LookIn | Уточняет область поиска. Список констант xlFindLookIn:
|
| LookAt | Поиск частичного или полного совпадения. Список констант xlLookAt:
|
| SearchOrder | Определяет способ поиска. Список констант xlSearchOrder:
|
| SearchDirection | Определяет направление поиска. Список констант xlSearchDirection:
|
| MatchCase | Определяет учет регистра:
|
| MatchByte | Условия поиска при использовании двухбайтовых кодировок:
|
| SearchFormat | Формат поиска – используется вместе со свойством Application.FindFormat. |
* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes.
В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.
Знаки подстановки для поисковой фразы
Условные знаки в шаблоне поисковой фразы:
- ? – знак вопроса обозначает любой отдельный символ;
- * – звездочка обозначает любое количество любых символов, в том числе ноль символов;
- ~ – тильда ставится перед ?, * и ~, чтобы они обозначали сами себя (например, чтобы тильда в шаблоне обозначала сама себя, записать ее нужно дважды: ~~).
Простые примеры
При использовании метода Range.Find в VBA Excel необходимо учитывать следующие нюансы:
- Так как этот метод возвращает объект Range (в виде одной ячейки), присвоить его можно только объектной переменной, объявленной как Variant, Object или Range, при помощи оператора Set.
- Если поисковая фраза в заданном диапазоне найдена не будет, метод Range.Find возвратит значение Nothing. Обращение к свойствам несуществующей ячейки будет генерировать ошибки. Поэтому, перед использованием результатов поиска, необходимо проверить объектную переменную на содержание в ней значения Nothing.
В примерах используются переменные:
- myPhrase – переменная для записи поисковой фразы;
- myCell – переменная, которой присваивается первая найденная ячейка, содержащая поисковую фразу, или значение Nothing, если поисковая фраза не найдена.
Пример 1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub primer1() Dim myPhrase As Variant, myCell As Range myPhrase = "стакан" Set myCell = Range("A1:L30").Find(myPhrase) If Not myCell Is Nothing Then MsgBox "Значение найденной ячейки: " & myCell MsgBox "Строка найденной ячейки: " & myCell.Row MsgBox "Столбец найденной ячейки: " & myCell.Column MsgBox "Адрес найденной ячейки: " & myCell.Address Else MsgBox "Искомая фраза не найдена" End If End Sub |
В этом примере мы присваиваем переменной myPhrase значение для поиска – "стакан". Затем проводим поиск этой фразы в диапазоне "A1:L30" с присвоением результата поиска переменной myCell. Далее проверяем переменную myCell, не содержит ли она значение Nothing, и выводим соответствующие сообщения.
Ознакомьтесь с работой кода VBA в случаях, когда в диапазоне "A1:L30" есть ячейка со строкой, содержащей подстроку "стакан", и когда такой ячейки нет.
Пример 2
Теперь посмотрим, как метод Range.Find отреагирует на поиск числа. В качестве диапазона поиска будем использовать первую строку активного листа Excel.
|
1 2 3 4 5 6 7 8 9 |
Sub primer2() Dim myPhrase As Variant, myCell As Range myPhrase = 526.15 Set myCell = Rows(1).Find(myPhrase) If Not myCell Is Nothing Then MsgBox "Значение найденной ячейки: " & myCell Else: MsgBox "Искомая фраза не найдена" End If End Sub |
Несмотря на то, что мы присвоили переменной числовое значение, метод Range.Find найдет ячейку со значением и 526,15, и 129526,15, и 526,15254. То есть, как и в предыдущем примере, поиск идет по подстроке.
Чтобы найти ячейку с точным соответствием значения поисковой фразе, используйте константу xlWhole параметра LookAt:
|
1 |
Set myCell = Rows(1).Find(myPhrase, , , xlWhole) |
Аналогично используются и другие необязательные параметры. Количество «лишних» запятых перед необязательным параметром должно соответствовать количеству пропущенных компонентов, предусмотренных синтаксисом метода Range.Find, кроме случаев указания необязательного параметра по имени, например: LookIn:=xlValues. Тогда используется одна запятая, независимо от того, сколько компонентов пропущено.
Пример 3
Допустим, у нас есть многострочная база данных в Excel. В первой колонке находятся даты. Нам необходимо создать отчет за какой-то период. Найти номер начальной строки для обработки можно с помощью следующего кода:
|
1 2 3 4 5 6 7 8 9 10 |
Sub primer3() Dim myPhrase As Variant, myCell As Range myPhrase = "01.02.2019" myPhrase = CDate(myPhrase) Set myCell = Range("A:A").Find(myPhrase) If Not myCell Is Nothing Then MsgBox "Номер начальной строки: " & myCell.Row Else: MsgBox "Даты " & myPhrase & " в таблице нет" End If End Sub |
Несмотря на то, что в ячейке дата отображается в виде текста, ее значение хранится в ячейке в виде числа. Поэтому текстовый формат необходимо перед поиском преобразовать в формат даты.
21/08/20
как задать поиск точного, но регистронезависимого значения ?
напр. дано
Иванов
диван
Иван
ивановский район
myPhrase = «иван»
машина должна найти 4-ое слово «Иван»
Привет, 0mega!
а как выделить цветом и/или проинформировать о том что это третья ячейка ?
Заливка найденной ячейки зеленым цветом, просмотр ее адреса и номера строки:
А как задать поиск точного, но регистрозависимого значения, с поиском совпадения внутри, но с учетом последних двух символов, в данном примере это _5?
напр. дано
Иванов_123
диван_123
Иван_2_5
Иван_3
Иван_321_3
ивановский район_521
myPhrase = “Иван, где в конце _5”
машина должна найти 3-е слово “Иван_2_5”
Привет, Иван! Так как в начале вопроса написано «А как задать поиск точного, но регистрозависимого значения…», я в предпоследней строке (myPhrase = “иван, где в конце _5”) исправил строчную «и» на заглавную.
Привет Евгений! Благодарю! Пусть остается регистронезависимый.
И хотелось бы увидеть еще один пример, где слово Иван — есть переменная, но с окончанием «*_5»
Предположим переменная — Dim myIvan As Range
Как в этом случае будет выглядеть запись синтаксиса?
Если оставить регистронезависимый поиск по частичному совпадению, тогда параметры поиска можно не указывать:
Здравствуйте! Помогите пожалуйста написать код … При условии, что есть диапазон со значениями дат, а нужно произвольную ячейку с датой сравнить с этим диапазоном. Если не находит совпадений то оставить эту дату в этой ячейке. Спасибо заранее
Здравствуйте. Если в искомом диапазоне есть два и > искомых значения и все эти значения нужно вывести, например, в MsgBox или debug.print. С помощью цикла задача выполняется достаточно легко. А как это сделать с помощью Find, ведь он ищет только первое значение, т.е. как-то нужно включить опцию «найти все» ?
Добрый день, Вадим!
Для продолжения поиска используйте метод Range.FindNext.
Как задать поиск после указанной строки?
Привет, Кирилл!
Для указания строки используйте параметр
After. Например, после 10 строки (то есть с 11-й):Здравствуйте.
Подскажите, а откуда информация что .Find работает быстрее циклов VBA?
Добрый день, Антон!
Это была ошибка. Тест на моем компьютере показал, что цикл For Each… Next находит значение в последней ячейке первого столбца за 1,53 сек., а метод Find – за 2,20 сек. Спасибо за обратную связь!
Здравствуйте, Евгений. Имеется таблица. В левой колонке — цифры(отфильтрованы в порядке возрастания). Задача-по значению первой колонке найти остальные параметры. Точного совпадения нет. Написать поиск в VBA.(что-то типа ИНДЕКС(ПОИСКПОЗ) или ВПР в Excel). Поиск с помощью FIND-ничего не дает. Требуется только точное совпадение. Поиск в цикле-та же песня. Подскажите пожалуйста как решить этот вопрос. Cпасибо.
Здравствуйте, Михаил!
Возможно, вам подойдет метод WorksheetFunction.Match (поиск позиции).
Помогите плизз. Есть 3 столбца. Надо оставить «Дебитор доставки» остальные удалить. Создаю 2 запроса одинаковых. С 3 столбцом все нормально а вот 1-2 удаляются вместе. И как задать в одном запросе удаление дебитора и юр. адрес клиента
(
Set A = .Find(What:=("Юр. адрес клиента","Дебитор"), LookIn:=xlValues, lookat:=xlPart)) — так не работает.Дебитор
Дебитор доставки
Юр. адрес клиента
Сделал. Сначала переименовал, а потом удалил.
Добрый день. Очень интересная статья
Подскажите пожалуйста, как добиться следующего цикла.
Например: надо найти ячейки со словом «ЖКХ» и «квартплата» в столбце А и проставить это наименование в столбец В.
А1 оплата ЖКХ
А2 квартплата за Иванова
А3 квартплата за ЖКХ
Соответственно в столбце В должно быть
В1 ЖКХ
В2 квартплата
В3 квартплата ЖКХ (или хотябы по первому поисковому слову ЖКХ)
Заранее спасибо.
Здравствуйте, Сергей!
Для поиска совпадений в количестве более одного вместе с методом Range.Find для продолжения поиска используется метод Range.FindNext:
В этом примере слово «ЖКХ» заменит слово «квартплата» в ячейке справа, если они оба будут найдены в одной ячейке слева.
Доброго времени суток, Евгений! Подскажите, пожалуйста, есть файл в нем надо найти скрытое слово, то есть мы не знаем что ищем. Как в таком случае искользовать VBA Find function? Спасибо!
Добрый день, Ainur!
Если искомое слово (или его часть) неизвестно, то найти его с помощью метода Find невозможно.
Евгений, огромное спасибо за помощь
Здравствуйте!
Спасибо за статью.
Может, кто знает: как найти ячейку по значению, если в ней формула? И в VBA, и в таблицах любопытно.
Добрый день!
Значения ищутся по умолчанию, не зависимо от того, есть ли в ячейке формула или нет. А вот чтобы найти формулу, необходимо указать явно, что ищется формула:
Спасибо.
Что нужно указать, чтобы найти ячейку, в которой формула, но ищется которая по значению?
Нужно проверить наличие формулы:
Т.е. .Find в этом случае не работает ∎
Ждал такого ответа.
Спасибо и примите восхищение поддержкой.
Евгений, здравствуйте! Огромное спасибо за статью. Подскажите пожалуйста, подойдет ли Find Range для решения следующей задачи:
Имеется некоторая база с неким продуктом, у каждого продукта свой каталожный номер, каталожные номера записаны в столбец, а в строках содержится остальная информация по товару (цена, производитель, срок гарантии, импортер и т.д.), можно ли использовать Find для автоматического заполнения строки после ввода каталожного номера в ячейку? Если совсем обывательским языком говорить, то программа должна делать следующее: введенное в ячейку значение сравнивает со всеми значениями ячеек в этом столбце, которые расположены выше изменяемой ячейки. Если каталожный номер (читай значение) совпадает с уже встречающимся выше каталожным номером, то программа автоматически заполняет строку значениями, которые соответствуют данному кат. номеру, в ином случае, когда совпадающего значения не найдено — оставляет строку пустой. Спасибо за ваше время и еще раз за вашу статью!
Здравствуйте, Дмитрий!
Вы можете использовать метод Range.Find для решения своей задачи. А если столбец с каталожными номерами является первым в таблице, вы также можете использовать метод WorksheetFunction.VLookup.
Добрый день.
Подскажите, как присвоить из таблицы нужное мне значение (Цена без НДС) ?
Прошу прощения, на работе нет программиста, приходится самому учиться(
Пример ниже не правильный.
Как правильно?
Здравствуйте, Виктор!
Вместо строки "Цена без НДС" должен быть указан номер столбца или его буквенное обозначение, например, "D".
Подскажите, пожалуйста, как найти число с форматированием группы знаков (например 15 678,99), само число 15678,99.
Если искать «15678,99», то ничего не находится, только если «15 678,99»?
Добрый день, Сергей!
Скорее всего, в вашем диапазоне к числам применен текстовый формат. Замените текстовый формат ячеек на числовой с разделителем групп разрядов, если он нужен. Если в коде VBA вы будете указывать искомое число вручную, заменяйте десятичную запятую на точку.
Спасибо за ответ.
Формат числовой. Если на странице в любую ячейку вписать 1567,89 потом применить форматирование ячейки как число с разделителем разрядов и двумя знаками после запятой, будет выглядеть как 1 567,89
Теперь, если искать значение (не формулу!) 1567 то ничего не найдется, а если «1 567», тогда выделяется наша ячейка(
Сергей, у меня ячейка выделяется. Сделал, как вы написали: вписал в ячейку 1567,89, потом применил форматирование — как число с разделителем разрядов и двумя знаками после запятой, получилось — 1 567,89. Проверьте с моим кодом:
Верно, по-умолчанию ищется с параметром LookIn:=xlFormulas, а мне нужно чтобы было LookIn:=xlValues, т.к. вместо числа в ячейке м.б. ссылка (=D3), в которой будет число 1567,89
Тогда код будет такой и ничего не найдется
Сергей, по умолчанию LookIn:=xlValues, но когда мы ищем число 1567, оно не равно числу 1567.89, поэтому метод Find его не находит. По частичному совпадению мы можем искать только строки. Используя метод Find, мы не можем указать, чтобы числовые значения ячеек воспринимались как текст. Но мы можем преобразовать числа в строки, проходя диапазон циклом:
Добрый день, не могли бы вы подсказать пожалуйста, как найти и очистить ячейки, в которых находится только один символ?
У меня данный символ является 0, но в других ячейках он присутствует и тексте, и удаляться ни в коем случае не должен, заранее благодарю
Добрый день!
Спасибо за ответ, но мая задача немного в другом: есть столбец чисел на листе1 a1=1500000, a2=1800000… отформатированы как числа с разделителем и 2 цифрами после запятой и эти числа надо найти последовательно на другом листе2, на котором они представлены формулами как ссылки на лист1 (c1 — «=Лист1!A1», c2 — «=Лист1!A2″…) так же отформатированы как числа с разделителем и 2 цифрами после запятой.
Я делаю так:
И у меня не получается их найти
Здравствуйте!
Можно добавить, что Find выдаёт ошибку, если на его пути встретятся объединённые ячейки.
Подскажите, а как найти ячейки и очистить, если в ячейке значение, определенного формата? Нужно найти ячейки от 7-ми до 11-ти значных.
Тоесть от 000000 до 99999999999…
указать формат «ddddddd» ?
Добрый день, Юрий!
Используйте такой вариант:
блин, точно, я забыл про IsNumeric
Спасибо.
Хотел уже пойти путем чуть другим, указав условие через Select Case:
А подойдет ли данный способ, если нужно скопировать столбец целиком, при наличии в нем определнной фразы на другой лист? И если подойдет, то как это будет выглядеть?
Здравствуйте, Александр!
Способ подойдет.
Пример определения номера столбца, в ячейке которого находится искомая фраза, и копирование найденного столбца на другой лист:
Здравствуйте. Подскажите, пожалуйста, почему так можно
With Range(Cells(1, i + 1), Cells(360, i + 1))а так выдает ошибку
With Worksheets("Обходы").Range(Cells(1, i + 1), Cells(360, i + 1))Обязательно With Range запускать с активного листа, с другого взять Range нереально?
Здравствуйте, Виктор!
У вас
Rangeотносится кWorksheets("Обходы"), аCellsк активному листу, поэтому возникает ошибка. Используйте такую конструкцию:С одним блоком With будет выглядеть так:
Обсуждение закрыто.