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

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

Содержание

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

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

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

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

Предназначение и синтаксис метода 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: 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 необходимо учитывать следующие нюансы:

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

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

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

Пример 1

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.

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. В первой колонке находятся даты. Нам необходимо создать отчет за какой-то период. Найти номер начальной строки для обработки можно с помощью следующего кода:

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

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

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

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

Виктор 19.01.2023 в 21:30

Здравствуйте. Подскажите, пожалуйста, как использовать .Find(myPhrase), если первая ячейка содержит дату, допустим 1.01.2023, а остальные =A2+1 и тд. находится только первая ячейка с датой, а в остальных ячейках формулы как даты не видит.
1. Евгений 19.01.2023 в 23:42
  
  Здравствуйте, Виктор!
  Приведите пример строки с фразой, я проверю.
Виктор 20.01.2023 в 20:24

Код взял из примера 3.
Dim myPhrase As Variant, myCell As Range myPhrase = 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 & " в таблице нет"
1. Евгений 20.01.2023 в 21:24
  
  Если в ячейках есть формулы, то поиск может пойти по ним. Укажите поиск по значениям явно:
  
  1
  
  Set myCell = Range("a1:a20").Find(myPhrase, LookIn:=xlValues)
Ivan 22.01.2023 в 08:29

Подскажите пожалуйста, при наличии списка именинников на каждый месяц, как выдать весь список именинников и как сделать запрос для обращения к месяцу?
Евгений 01.02.2023 в 08:39

Евгений, Здравствуйте.
Не могу разобраться как найти «нулевые» даты (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 "Готово"

Евгений 01.02.2023 в 21:04

Здравствуйте, Евгений!
Попробуйте так, только код запускайте через кнопку, при запуске из редактора он будет очень долго работать или даже зависнет:

Sub Primer()

Dim rg As Range, arr() As Variant, e As Variant, u As Long, n As Long

Set rg = Range("a3:dp1004")

arr = rg.Value

u = UBound(arr)

For Each e In arr

If IsDate(e) And e = 0 Then

rg((n Mod u) + 1, (n \ u) + 1).ClearContents

End If

n = n + 1

MsgBox "Готово"

End Sub

1. Евгений 02.02.2023 в 07:53
  
  Все отлично работает, быстро (с кнопки). Спасибо большое!
  А то я не придумал ничего больше, кроме как прописать все диапазоны столбцов с датами. Поменять в них формат ячеек (.NumberFormat = "@"), удалить нули (.Replace What:="0", Replacement:="", LookAt:=xlWhole), и затем уже вернуть формат обратно (.NumberFormat = "dd.mm.yyyy")

Обсуждение закрыто.