CoderNotes - заметки программиста

Публикации  »  VBA, Excel
Бесплатный хостинг + SSL-сертификат

Зарегистрируйте домен и получите 2 месяца бесплатного хостинга и SSL-сертификат на 1 год в подарок

Подробнее
GeekBrains

Поиск на листе Excel

Поиск какого-либо значения в ячейках Excel довольно часто встречающаяся задача при программировании какого-либо макроса. Решить ее можно разными способами. Однако, в разных ситуациях использование того или иного способа может быть не оправданным. В данной статье я рассмотрю 2 наиболее распространенных способа.

 

Поиск перебором значений

Довольно простой в реализации способ. Например, найти в колонке "A" ячейку, содержащую "123" можно примерно так:

Sheets("Данные").Select
For y = 1 To Cells.SpecialCells(xlLastCell).Row
    If Cells(y, 1) = "123" Then
        Exit For
    End If
Next y
MsgBox "Нашел в строке: " + CStr(y)

Минусами этого так сказать "классического" способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.

Поиск функцией Find

Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую "123" достаточно такого кода:

Sheets("Данные").Select
Set fcell = Columns("A:A").Find("123")
If Not fcell Is Nothing Then
    MsgBox "Нашел в строке: " + CStr(fcell.Row)
End If

Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист "Данные";
2-я строка: Осуществляем поиск значения "123" в колонке "A", результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае - будет пустой, т.е. Nothing.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What - Строка с текстом, который ищем или любой другой тип данных Excel

After - Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.

LookIn - Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

LookAt - Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

SearchOrder - Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

SearchDirection - Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

MatchCase - Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

MatchByte - Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)

SearchFormat - Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

Чтобы продолжить поиск, можно использовать FindNext (искать "далее") или FindPrevious (искать "назад").

Примеры поиска функцией Find

Пример 1: Найти в диапазоне "A1:A50" все ячейки с текстом "asd" и поменять их все на "qwe"

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", LookIn:=xlValues)
  Do While Not c Is Nothing
    c.Value = "qwe"
    Set c = .FindNext(c)
  Loop
End With

Обратите внимание: Когда поиск достигнет конца диапазона, функция продолжит искать с начала диапазона. Таким образом, если значение найденной ячейки не менять, то приведенный выше пример зациклится в бесконечном цикле. Поэтому, чтобы этого избежать (зацикливания), можно сделать следующим образом:

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

В ниже следующем примере используется другой вариант продолжения поиска - с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.

Пример 3: Продолжение поиска с использованием Find с параметром After.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .Find("asd", After:=c, lookin:=xlValues)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом "курсив" и поменять их формат на обычный (не "курсив")

lLastRow = Cells.SpecialCells(xlLastCell).Row
lLastCol = Cells.SpecialCells(xlLastCell).Column
Application.FindFormat.Font.Italic = True
With Worksheets(1).Range(Cells(1, 1), Cells(lLastRow, lLastCol))
  Set c = .Find("", SearchFormat:=True)
  Do While Not c Is Nothing
    c.Font.Italic = False
    Set c = .Find("", After:=c, SearchFormat:=True)
  Loop
End With

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка - цикл пока результат поиска не будет пустым. 7-я строка - меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать "защиту от зацикливания", как в Примерах 2 и 3, т.к. шрифт меняется и после "прохождения" по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Свойство FindFormat можно задавать разными способами, например, так:

With Application.FindFormat.Font 
  .Name = "Arial" 
  .FontStyle = "Regular" 
  .Size = 10 
End With

Следующий пример - применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.

Пример 5: Найти последнюю колонку и столбец, заполненные данными

Set c = Worksheets(1).UsedRange.Find("*", SearchDirection:=xlPrevious)
If Not c Is Nothing Then
  lLastRow = c.Row: lLastCol = c.Column 
Else
  lLastRow = 1: lLastCol = 1
End If
MsgBox "lLastRow=" & lLastRow & " lLastCol=" & lLastCol

В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т.ч. и те, что были использованы ранее, а сейчас пустые. Функция Find ищет ячейку с любым значением с конца диапазона.

При поиске можно так же использовать шаблоны, чтобы найти текст по маске, следующий пример это демонстрирует.

Пример 6: Выделить красным шрифтом ячейки, в которых текст начинается со слова из 4-х букв, первая и последняя буквы "т", при этом после этого слова может следовать любой текст.

With Worksheets(1).Cells
  Set c = .Find("т??т*", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Color = RGB(255, 0, 0)
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

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

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

  • Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не "01.03.2018"
  • В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

Приведу несколько примеров поиска даты.

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

d = Date
Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Пример 8: Найти 1 марта 2018 г.

d = #3/1/2018#
Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Искать часть даты - сложнее. Например, чтобы найти все ячейки, где месяц "март", недостаточно искать "03" или "3". Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел - это выбрать формат в котором месяц прописью для ячеек с датами и искать слово "март" в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

d = #3/1/1900#
Set c = Cells.Find(Format(d, "m\/d\/"), LookIn:=xlFormulas, LookAt:=xlPart)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

 

Категория: VBA, Excel

Книги по теме:

Виктор Долженков

Microsoft Office Excel 2010

Андрей Ветров

Excel 2013-2016

Лада Рудикова

Microsoft Office Excel 2016

Посмотреть все книги по программированию

Комментарии к статье:

10.09.17   Дмитрий Очень толковая и полезная статья. Помогла мне существенно ускорить мой код. Спасибо!
23.11.17   Гость Спасибо, хорошая статья.
03.12.17   Владимир Спасибо! Использую в своих проектах...
07.12.17   Эд Спасибо, очень пригодилась Ваша статья!
19.01.18   Николай .find не ищет значение в ячейке, если ячейка в скрытой строке.
.match позволяет не беспокоится о том, что на листах с источниками данных строка с искомым значением будет скрыта из-за установленного фильтра.
05.02.18   Владимир Большое спасибо! Очень толково и понятно.
11.03.18   Гость Здравствуйте,
А если мне требуется найти ячейку в определенном столбце с определенным значением, например "строка 1" и если я нахожу такую, то через несколько строк(неизвестно сколько) мне нужно раскрасить следующую ячейку в другом столбце(самую ближайшую) и остановить цикл, и опять по новой искать дальше "строка 1". Можете посоветовать?
26.03.18   Гость Спасибо! Все бы так описывали! Все доступно и понятно))
23.05.18   Аркадий В VBA я новичок. Активно использую интернет для своих вопросов, однако таких информативных, лаконичных и простых в понимании сайтов не много. Огромное спасибо автору! Адрес уже в закладках.
21.07.18   Гость Спасибо! Уже несколько недель искала подобное!
25.07.18   Joann Метод .find (в случае далнейших множественных обращений к этому механизму) разумно вынести в отдельную функцию, как из управляющей процедуры передать в эту функцию ее параметры (обязательный текстовый параметр 'what:=' - передается без проблем, а вот значения ('xlValues', 'xlWhole', ...) для 'LookIn:=', 'LookAt:=', ... - приводят к ошибке)?
15.08.18   Марат Спасибо за статью, пополнил свои знания в части метода Find. Простые переборы хороши на небольших диапазонах. А когда нужно обработать сотни тысяч ячеек, Find - хороший инструмент!
28.08.18   Гость Познавательно и подробно. Спасибо за статью.
16.10.18   Гость Хорошая статья. Спасибо
29.10.18   Sega Полезная статейка. Спасибо.
14.11.18   Гость Статья для начинающих, а тонкости поиска по дате нет ни одного примера
02.02.19   Ибрагим Чушь полная, плагиат!
01.03.19   inexsu.wordpress.com Loop While Not c Is Nothing And c.Address <> firstResult
Когда c станет Nothing, c.Address даст ошибку
Решение https://inexsu.wordpress.com/2018/03/05/range-findnext-method/
01.03.19   Администратор Вы правы, такое действительно может произойти, например, если менять значения найденных ячеек. Т.е., например, ищем значения "asd" и меняем их на "qwe". При замене последнего значения, FindNext ничего уже не найдет, вернет Nothing и произойдет ошибка в условии.
Внес изменения в статье. Спасибо вам за подсказку.
07.04.19   Гость Добрый день. есть 2 таблица на разных листах. макрос нашел нужные ячейки с ИНН по 1 условию, затем мне надо чтобы макрос нашел в таблице 2 на листе 2 все названия клиентов с инн по условию 1 из таблицы 1 с листа 1. как тогда использовать find.
07.04.19   Гость Лучше использовать функцию ВПР
21.05.19   Гость Очень подробная статья. Отдельное спасибо за множество примеров.
17.10.19   Михаил Спасибо большое все понятно доступно и очень полезно
31.10.19   Гость Спасибо!!!
01.11.19   Гость ку
01.11.19   Гость 2 раза
19.11.19   Kamol Отлично.
Поиск с двумя значениями Пример в колонке А="123" и В="456"
25.12.19   Гость Спасибо!
05.03.20   Гость Добрый день!
Спасибо за код автору
Вопрос

Как сделать поиск слова по, но с любым регистром и любым набором символов
Пример: Я ввожу слово через Textbox
В таблице есть слово компьютер, колодец, ком
Нужно, чтобы программа находила по запросу "омпьюте", "пьюте" слово компьютер а по запросу "ком" слова компьютер и ком
19.05.20   ГостьSpenser.Hebe as long as the kung fu is deep, the iron shovel is ground into a needle.
07.06.20   Светлана Добрый день! Подскажите, пожалуйста, в примере №7 "Найти текущую дату..."
как найти ячейку с текущей датой и выделить эту ячейку цветом? Очень надо!!!
07.06.20   Светлана Всё сделала. Спасибо за примеры.
19.06.20   Гость Статья толковая, жаль своего примера не нашел
11.09.20   Гость Спасибо!
18.10.20   Гость Спасибо, очень толково и полезно!!
09.11.20   Александр 1. В примерах 2, 3 и 6 проверка "If c Is Nothing Then Exit Do" не нужна, потому что НИКОГДА не сработает и не приведёт к выходу из цикла. Раз мы попали в цикл - значит была найдена хотя бы одна ячейка с искомым значением и значит последующий поиск заведомо даст результат, даже если такая ячейка всего одна.
Значения в ячейках не меняются, поэтому для выхода из цикла достаточно условия "c.Address = firstResult". А это условие будет выполнено, как только поиск вернётся на первую найденную ячейку с искомым значением.
2. В примере 3 продолжение поиска выполнено оператором "Set c = .Find("asd", After:=c, lookin:=xlValues)". Такой вариант, разумеется, работает, но на мой взгляд лишь затуманивает головы. В ДАННОМ СЛУЧАЕ надо не городить огород, а воспользоваться оператором FindNext, который и предназначен для того, чтобы продолжить поиск с ячейки, следующей после последней найденной: "Set c = .FindNext(c)". Я исхожу из простого принципа: если в языке есть специальный инструмент для выполнения специальных действий, то эти действия и надо выполнять с помощью этого инструмента.
Гвоздь можно забить пассатижами, но для этого больше подойдёт молоток )
24.11.20   Гость " Добрый день!
Спасибо за код автору
Вопрос

Как сделать поиск слова по, но с любым регистром и любым набором символов
Пример: Я ввожу слово через Textbox
В таблице есть слово компьютер, колодец, ком
Нужно, чтобы программа находила по запросу "омпьюте", "пьюте" слово компьютер а по запросу "ком" слова компьютер и ком"

Можно использовать аргумент функции Find. Аргумент - LookAt:=xlPart.
Он будет искать все значения, которые имеют в себе заданное "буквосочетание"
10.02.21   Гость Добрый день. Помогите с макросом или может это можно реализовать при помощи формулы. Есть диапазон ячеек: 2000 рядов, 1 столбец с датой и 20 столбцов с числами от 1 до 80. Количество пар чисел вроде 3160. Начиная с первой строки нужно найти строку с которой находится пара ячеек, которая встречается позже всех остальных пар. Можно ли такое сделать с тройками и четверками ячеек?
02.04.21   Гость Спасибо за статью.
А можно ли найти слово, которое встречается несколько раз на первом листе, при этом на другом листе будет написана цифра сколько раз это слово нашлось?
28.04.21   Андрей Классная статья
01.05.21   Гость Имеется рабочая таблица в которую нужно добавить данные из текстового файла . Макрос заливки текстового файла в excel я прописал. Далее сделал добавление новой строки с текущей датой . А вот как обратиться к определенному столбцу чтобы формулой впр подтянуть данные в столбец ( например d) моих знаний уже не хватает . Как обратиться по названию столбца? ( хочу вставить формулу впр в столбец d , например )
11.06.21   Виталий Добрый день. Подскажите такой момент: у меня при нахождении определенного текста в нужную ячейку вставляется значение ячейки, в которой найден искомый текст.
Мне нужно, что если текст не находится, та ячейка куда вставляется значение становилась пустой. А я никак не могу понять как это реализовать вот здесь If c Is Nothing Then Exit Do

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