Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel найти ячейку со значением

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

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

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

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

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

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

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

Вкратце опишу что делают строчки данного кода:
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»

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

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

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

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

Читать еще:  Использование массивов в excel

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

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

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

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

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

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

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

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

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

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

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

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

— для обозначения символов *, ? и

. (т.е. чтобы искать в тексте вопросительный знак, нужно написать

?, чтобы искать именно звездочку (*), нужно написать

* и наконец, чтобы найти в тексте тильду, необходимо написать

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

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

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

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

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

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

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

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

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

Vba excel найти ячейку со значением

khall » 23.02.2006 (Чт) 9:51

Читать еще:  Число в excel

Помогите, пожалуйста, с задачкой.
Как найти ячейку со значением, например, город, и очистить эту ячейку, а также зависимые с ней ячейки, расположенные на той же строке, что и найденная ячейка в диапазоне G8:G43 и E8:E43?

Этот код очищает все найденные ячейки со значением город, но не очищает те ячейки, которые находятся в той строке, что и ячейка со значением город.
Sub Поиск()

Dim c As Range
For Each c In [I8:I43]
If c.Value Like «город» Then
c.Value = «»
End If
Next
End Sub

Может быть так или есть код покрасивее? При этом остается проблема: если курсор не находится в ячейке диапазона I8:J20, появляется сообщение об ошибке. Ошибка появляется и тогда, когда в ячейках больше нет данных в диапазоне I8:J20. Как это все исправить?

Public Sub Поиск2()

Dim rng As Range

Set rng = Range(«I8:J20″).Find(What:=»город», After:=ActiveCell, LookIn:=xlValues, _
MatchCase:=True)
Range(«I8:J20»).FindNext(After:=ActiveCell).Activate

If Not (rng Is Nothing) Then
Cells(ActiveCell.Row, ActiveCell.Column — 1).Select
ActiveCell = «»
Cells(ActiveCell.Row, ActiveCell.Column — 1).Select
ActiveCell = «»
Cells(ActiveCell.Row, ActiveCell.Column + 4).Select
ActiveCell = «»
Else
MsgBox «Не найдено значение»
End If

Ниже все в одном файле

GSerg » 23.02.2006 (Чт) 11:38

Код: Выделить всё Sub sdfsdf()
Dim f As Excel.Range, w As Excel.Range

Set w = Range(«I8:I43»)
Set f = w.Find(«город», , xlValues, xlWhole)

Do Until f Is Nothing
Range(f.Offset(0, -4), f).Value = Empty
Set f = w.FindNext
Loop
End Sub

Усовершенствование кода предыдущей задачи

khall » 24.02.2006 (Пт) 8:31

Код работает просто СУППЕР! Я его поместил в стандартный модуль Module1 с названием процедуры Поиск. К нему обращаются процедуры рабочих листов при наступлении события Worksheet_Change любого активного рабочего листа через Call Поиск. Теперь при наступлении изменения в ячейках D62 или D63, а соответственно и D65, автоматически удаляются названия «Телефон» и «город», а также сумма в грн. Это приводит к появлению только одной записи. Это мне и нужно!

Вот код рабочего листа Лист1. Такой же код присутствует и в других листах: Лист2.

В будущем, пожалуйста, проявляйте уважение к читающим и пользуйтесь форматированием кода.
Код: Выделить всё Private Sub Worksheet_Change(ByVal Target As Range)

Dim тф As Range, i As String
Set тф = Range(«D62:D65»)

If Not (Application.Intersect(Target, тф) Is Nothing) Then
Call Поиск

i = 8
While Sheets(«Лист1»).Range(«E» + LTrim$(Str$(i))) <> «»
i = i + 1
Wend
Sheets(«Лист1»).Range(«E» + LTrim$(Str$(i))) = Range(«D65»).Value
Sheets(«Лист1»).Range(«G» + LTrim$(Str$(i))) = «Телефон»
Sheets(«Лист1»).Range(«I» + LTrim$(Str$(i))) = «город»
End If

Стандартный модуль
Код: Выделить всё Public Sub Поиск()

Dim f As Excel.Range, w As Excel.Range

Set w = Range(«I8:J20»)
Set f = w.Find(«город», , xlValues, xlWhole)

Do Until f Is Nothing
Range(f.Offset(0, -4), f).Value = Empty
Set f = w.FindNext
Loop
End Sub

Но я обнаружил особенность, которую теперь нужно устранить.
Иногда может получиться, что или ячейка D62 или D63 могут стать пустыми, что сделает пустой и ячейку D65. В этом случае значение ячейки, содержащей сумму расхода в грн. «Телефон» и «город», очищается, но остаются названия «Телефон» и «город» в ячейках столбцов G и I.

ВОПРОС: Как сделать, чтобы при удалении значения из ячейки с суммой в грн. удалялись и теперь ненужные записи «Телефон» и «город», которые были связаны с удаленной записью суммы в грн.

Как усовершенствовать код?

GSerg » 24.02.2006 (Пт) 13:21

khall » 24.02.2006 (Пт) 14:09

GSerg » 24.02.2006 (Пт) 14:48

khall » 24.02.2006 (Пт) 15:04

Спасибо тебе за терпение! Я только учусь!
Кое-что будет экспериментом.
Во всяком случае, спасибо за твой код, который работает исключительно.
Код: Выделить всё Public Sub Поиск()

Dim f As Excel.Range, w As Excel.Range

Set w = Range(«I8:J20»)
Set f = w.Find(«город», , xlValues, xlWhole)

Do Until f Is Nothing
Range(f.Offset(0, -4), f).Value = Empty
Set f = w.FindNext
Loop
End Sub

Читать еще:  Какой браузер лучше для просмотра видео

khall » 24.02.2006 (Пт) 15:05

GSerg » 24.02.2006 (Пт) 15:11

khall » 24.02.2006 (Пт) 16:12

Igor[hw] » 14.08.2006 (Пн) 13:25

А как можно сделать поиск двух разных слов например «город» и «улица» ?

alibek » 14.08.2006 (Пн) 13:28

Igor[hw] » 14.08.2006 (Пн) 15:15

Vladimir87 » 08.11.2006 (Ср) 21:25

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

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

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

Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.

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

Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.

В скобках перечислены параметры метода, среди них только What является обязательным.

Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.

Параметры метода 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 .
** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.

В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.

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

Условные знаки в шаблоне поисковой фразы:

  • ? – знак вопроса обозначает любой отдельный символ;
  • * – звездочка обозначает любое количество любых символов, в том числе ноль символов;

Простые примеры

При использовании метода Range.Find в VBA Excel необходимо учитывать следующие нюансы:

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

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

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