Метод 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 |
Несмотря на то, что в ячейке дата отображается в виде текста, ее значение хранится в ячейке в виде числа. Поэтому текстовый формат необходимо перед поиском преобразовать в формат даты.
Здравствуйте. Подскажите, пожалуйста, как использовать .Find(myPhrase), если первая ячейка содержит дату, допустим 1.01.2023, а остальные =A2+1 и тд. находится только первая ячейка с датой, а в остальных ячейках формулы как даты не видит.
Здравствуйте, Виктор!
Приведите пример строки с фразой, я проверю.
Код взял из примера 3.
Dim myPhrase As Variant, myCell As RangemyPhrase = Cells(2, 1)
Set myCell = Range("a1:a20").Find(myPhrase)
If Not myCell Is Nothing Then
MsgBox "Номер начальной строки: " & myCell.Row
Else: MsgBox "Даты " & myPhrase & " в таблице нет"
End If
в строке Cells(2, 1) стоит дата 10.01.2023, ее код находит
в строке Cells(3, 1) стоит формула =A2+1 (я ее вижу на листе как дату 11.01.2023) Если поставить вместо myPhrase = Cells(2, 1)
myPhrase = Cells(3, 1) нажимая F8 и проходя пошагово я вижу в переменной myPhrase дату 11.01.2023, но код пролетает на строку Else:
MsgBox "Даты " & myPhrase & " в таблице нет"Если в ячейках есть формулы, то поиск может пойти по ним. Укажите поиск по значениям явно:
Подскажите пожалуйста, при наличии списка именинников на каждый месяц, как выдать весь список именинников и как сделать запрос для обращения к месяцу?
Евгений, Здравствуйте.
Не могу разобраться как найти «нулевые» даты (00.01.1900)
Может вы подскажете как их все удалить в определенном диапазоне.
'Ищем "0" (00.01.1900) даты
FindZeroDate:
Dim myPhrase25 As Variant
myPhrase25 = "00.01.1900"
myPhrase25 = CDate(myPhrase25)
Set myCell = ThisWorkbook.Worksheets("WL").Range("a3:dp1004").Find(myPhrase25, , , xlWhole)
If Not myCell Is Nothing Then
myCellAddress = Replace(myCell.Address, "$", "")
Range(myCellAddress).ClearContents
GoTo FindZeroDate
Else
'MsgBox "Готово"
Здравствуйте, Евгений!
Попробуйте так, только код запускайте через кнопку, при запуске из редактора он будет очень долго работать или даже зависнет:
Все отлично работает, быстро (с кнопки). Спасибо большое!
А то я не придумал ничего больше, кроме как прописать все диапазоны столбцов с датами. Поменять в них формат ячеек (
.NumberFormat = "@"), удалить нули (.Replace What:="0", Replacement:="", LookAt:=xlWhole), и затем уже вернуть формат обратно (.NumberFormat = "dd.mm.yyyy")