Поиск на листе 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