Vba excel найти значение в столбце

Обновлено: 24.11.2024

В этом посте рассказывается все, что вам нужно знать о функции поиска VBA. Он объясняет, как использовать Find, простыми словами. В нем также есть множество примеров кода для поиска, которые вы можете использовать прямо сейчас.

Если вы хотите сразу перейти к примеру поиска, ознакомьтесь со статьей Как выполнить простой поиск.

Если вы хотите искать текст в строке, вам нужны функции InStr и InStrRev.

Если вы хотите найти последнюю строку или столбец с данными, перейдите к разделу Поиск последней ячейки, содержащей данные

Загрузить исходный код

Что такое функция поиска VBA?

Функция Find является членом Range.
Ищет диапазон ячеек, содержащих заданное значение или формат.
По сути, это то же самое, что и использование диалогового окна поиска на листе Excel.

Введение

Диалоговое окно поиска в Excel

Чтобы просмотреть диалоговое окно поиска Excel, перейдите на ленту «Главная» и нажмите «Найти и выбрать» в разделе «Редактирование». В появившемся меню выберите «Найти» (сочетание клавиш Ctrl + F)

При этом появится следующее диалоговое окно:

Функция поиска VBA использует большинство опций, которые вы видите в этом диалоговом окне.

Как использовать параметры поиска

Чтобы использовать параметры, вы передаете их в качестве параметров функции поиска. Это похоже на то, как вы используете функции рабочего листа. Например, функция Sum имеет диапазон в качестве параметра. Это означает, что вы указываете диапазон при его использовании.

Поиск VBA использует параметры таким же образом. Вы должны дать ему предмет, который вы ищете. Это первый параметр, и он обязателен.

Остальные параметры являются необязательными. Если вы их не используете, то Find будет использовать существующие настройки. Вскоре мы узнаем об этом подробнее.

В таблице в следующем разделе показаны эти параметры. В следующих разделах приводятся примеры и подробные сведения о том, как использовать эти параметры.

Параметры поиска VBA

В следующих таблицах показаны все параметры поиска.

< tbody > < /tr>

Параметр	Тип	Описание	Значения
Что	Требуется	Значение, которое вы ищете	Любой тип данных VBA, например String, Long
После	Необязательно	Диапазон одной ячейки, с которого вы начинаете поиск	Range("A5")
LookIn	Необязательно	Что искать, например Формулы, значения или комментарии	xlValues, xlFormulas, xlComments
LookAt	Необязательно	Посмотреть часть или вся ячейка	xlWhole, xlPart
SearchOrder	Необязательно	Порядок search	xlByRows или xlByColumns.
SearchDirection	Необязательно	Направление поиска	xlNext, xlPrevious
MatchCase	Необязательно	Если поиск чувствителен к регистру	True или False
MatchByte	Необязательно	Используется для двухбайтовых языков	True или False
Формат поиска	Необязательно	Разрешить поиск по формату. Формат задается с помощью Application.FindFormat	True или False

Важное примечание о параметрах поиска

Имейте в виду следующее, так как это может вызвать много разочарований при использовании функции поиска.

Как видно из таблицы, большинство параметров поиска VBA являются необязательными. Как мы уже говорили ранее, если вы не установите параметр поиска, он будет использовать существующую настройку.

Например, если вы установите для параметра LookIn значение xlComments, он будет искать значение только в комментариях. В следующий раз, когда вы запустите функцию поиска (либо из диалогового окна, либо из VBA), существующей настройкой LookIn будут комментарии.

В следующем коде показан пример этого

Это относится к параметрам LookIn, LookAt, SearchOrder и MatchByte.

Поиск возвращаемого значения

Если элемент поиска найден, функция "Найти" возвращает ячейку со значением. То есть он возвращает тип диапазона одной ячейки.

Если элемент поиска не найден, функция "Найти" возвращает объект, для которого задано значение "Ничего".

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

Как выполнить простой поиск

Начнем с простого примера поиска VBA. При использовании функции поиска вам понадобятся три вещи

Диапазон для поиска
Значение, которое вы ищете
Диапазон для хранения возвращаемой ячейки.

Возьмем следующие примеры данных

Мы будем искать текст «Jena» в ячейках с A1 по A5.

Следующий код ищет «Jena». Когда он находит «Jena», он помещает ячейку в переменную rgFound.

Приведенный выше код показывает самый простой поиск, который вы можете выполнить. Если вы впервые используете функцию поиска VBA, я рекомендую вам попрактиковаться на простом примере, подобном этому.

Если вы хотите попробовать эти примеры, вы можете скачать рабочую тетрадь в верхней части этого поста.

Если значение не найдено

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

Следующий код выдаст эту ошибку, если текст «Джон» не будет найден в диапазоне от A1 до A5

Нам нужно проверить возвращаемое значение, как показано в следующем коде

Использование «После» с поиском

Параметр После используется, если вы хотите начать поиск с определенной ячейки. При использовании диалогового окна поиска Excel активной ячейкой считается ячейка После. Другими словами, эта ячейка является отправной точкой для поиска. В VBA, если параметр После не указан, поиск начинается с верхней левой ячейки диапазона.

Пример 1 без после

Давайте посмотрим на следующий код.

Поиск вернет ячейку A2, так как именно здесь найдена первая «Рахаль».

Пример 2. Использование после

В следующем примере мы используем after. Мы говорим VBA начать поиск «Рахаль» после ячейки A2

Это вернет ячейку A6

Пример 3. Обтекание

Если совпадение не найдено, поиск будет "зацикливаться". Это означает, что он вернется к началу диапазона.

В следующем примере мы ищем Drucilla. Начинаем поиск После ячейки A2. Поиск ищет от A3 до A6, а затем переходит к A1.

Таким образом, следующий код вернет A1, так как нет текста «Друцилла» от A3 до A6:

Порядок поиска для этого примера: A4, A5, A6, A1.

Вы можете сами попробовать эти примеры, загрузив книгу из верхней части поста.

Использование LookIn с поиском

Использование LookIn позволяет выполнять поиск по значениям, формулам или комментариям.

Важное примечание. Если в ячейке есть только текст, этот текст считается формулой И значением. Подробнее см. в таблице ниже

< td >Apple

Ячейка содержит	Результат	Значение LookIn равно
Apple	Apple	Значение и формула
="App" & "le"'	Только значение
=LEFT("Apple",4)'	Appl	Только формула

Мы будем использовать следующие образцы данных.

A2 Содержит «Apple» только как значение
A3 Содержит «Apple» только как формулу
A4 Содержит «Apple» только в комментарии

Приведенный ниже код ищет «Apple» в различных типах: значение, формула, связанный комментарий и примечание.

Чтобы увидеть рабочий пример этого кода, вы можете загрузить исходный код из верхней части этого сообщения.

Важное примечание: я использовал xlCommentsThreaded для третьего, поскольку в Office 365 используются цепочечные комментарии. Если вы используете более старую версию, в которой нет цепочек комментариев, используйте Комментарии xl.

Использование LookAt с поиском

Использовать функцию LookAt довольно просто.

xlWhole означает, что значение поиска должно соответствовать всему содержимому ячейки.
xlPart означает, что значение поиска должно соответствовать только части ячейки.

В следующем примере слово «Apple» есть как часть содержимого ячейки в ячейке A2, и это полное содержимое в ячейке A3.

Первый поиск в следующем коде находит «Apple» в ячейке A2. Второй поиск ищет полное совпадение, поэтому находит A3.

Вы можете сами попробовать эти примеры, загрузив книгу из верхней части поста.

Использование SearchOrder с поиском

Параметр SearchOrder позволяет выполнять поиск по строке или по столбцу. В следующих примерах данных у нас есть два вхождения текста «Элли».

Если мы будем искать по строке, мы сначала найдем «Элли» в ячейке B2. Это потому, что мы ищем в строке 1, затем в строке 2 и т. д.

Если мы будем искать по столбцу, мы сначала найдем «Элли» в A5. Это потому, что мы ищем в столбце заказа A, столбце B и т. д.

В следующем коде показан пример использования SearchOrder с этим образцом данных

Использование SearchDirection с поиском

SearchDirection позволяет выполнять поиск вперед или назад. Итак, представьте, что у вас есть диапазон A1:A7. Поиск с использованием xlNext будет выполняться по порядку

A1, A2, A3, A4, A5, A6, A7

Поиск с использованием xlPrevious будет выполняться по порядку

A7, A6, A5, A4, A3, A2, A1

Использование xlNext с демонстрационными данными вернет A2 в качестве первого совпадения. Использование xlPrevious вернет A6.

Использование xlPrevious с After

Если вы используете параметр «После» с xlPrevious, тогда он начнется раньше из ячейки «После». Поэтому, если мы установим для ячейки «После» значение A6, порядок поиска будет

В следующем коде показан пример этого

Использование MatchCase с поиском

Параметр MatchCase используется для определения того, имеет ли значение регистр букв при поиске. Можно установить значение True или False.

Верно — регистр букв должен совпадать
False — регистр букв не имеет значения

В следующем примере списка есть две записи для «Элли». Во второй строчная буква е

В следующих примерах кода показан результат установки для MatchCase значений True и False

Использование MatchByte с поиском

Параметр MatchByte используется для языков с двухбайтовым набором символов. Это такие языки, как китайский/японский/корейский.

Если вы их не используете, этот параметр не имеет значения. Они используются следующим образом

True означает, что двухбайтным символам соответствуют только двухбайтовые символы.
False означает, что двухбайтовые символы могут совпадать с однобайтовыми или двухбайтовыми символами.

Использование подстановочного знака

Мы можем использовать символ звездочки (*) в качестве подстановочного знака при поиске текста. Звездочка представляет собой один или несколько символов.
Например,
“T*” найдет любое слово, начинающееся с T.
“To*” найдет любое слово, начинающееся с To.
“*y” найдет любое слово, оканчивающееся на y.
«*ey» найдет любое слово, оканчивающееся на ey.

В приведенном ниже коде показаны примеры использования подстановочного знака на основе этих данных:

Использование SearchFormat с поиском

Формат поиска немного отличается от других параметров. Он позволяет выполнять поиск по формату ячейки, например по типу шрифта или цвету ячейки.

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

В следующем примере данных у нас есть две отформатированные ячейки. Для ячейки A5 выбран жирный шрифт, а для ячейки A6 — красный цвет заливки.

Следующий код ищет ячейку, выделенную жирным шрифтом:

Использование подстановочного знака с форматом

Вы можете искать ячейку только по формату. Другими словами, значение в ячейке игнорируется при поиске. Вы делаете это, помещая «*» в строку поиска.

Следующий код ищет отформатированную ячейку — цвет ячейки в этом примере установлен красный. Содержимое ячейки не имеет значения:

Важно! Формат очистки

Когда вы устанавливаете атрибуты FindFormat, они остаются на своих местах до тех пор, пока вы не установите их снова. Это то, на что следует обратить внимание.

Например, представьте, что вы выбрали полужирный формат, а затем используете функцию "Найти". Затем вы устанавливаете формат на размер шрифта 12 и снова используете «Найти». Поиск будет искать ячейки, где шрифт выделен жирным шрифтом И имеет размер 12.

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

Вы можете видеть, что мы использовали это во втором примере SearchFormat выше.

Несколько поисков

Во многих случаях вам потребуется искать несколько вхождений одного и того же значения. Для этого сначала используем функцию «Найти».Затем мы используем функцию .FindNext, чтобы найти следующий элемент.

.FindNext выполняет поиск на основе настроек, которые мы использовали в файле Find. В следующем коде показан простой пример поиска первого и второго вхождений текста «Элли».

Иногда вы не знаете, сколько вхождений. В этом случае мы используем цикл для продолжения поиска, пока не найдем все элементы.

Мы используем Find, чтобы получить первый элемент. Если мы находим элемент, мы используем Do Loop с .FindNext, чтобы найти остальные вхождения.

FindNext будет повторяться. То есть после того, как он найдет A9, он продолжит поиск в A1. Поэтому мы сохраняем адрес первой найденной ячейки. Когда FindNext снова возвращает эту ячейку, мы знаем, что нашли все элементы.

Следующий код найдет все вхождения Элли

Вывод этого кода:
Найдено: $A$2
Найдено: $A$5
Найдено: $A$8

Поиск последней ячейки, содержащей данные

Очень распространенной задачей в VBA является поиск последней ячейки, содержащей данные в строке или столбце. Это не использует функцию поиска VBA. Вместо этого мы используем следующий код, чтобы найти последнюю строку с данными

Чтобы найти последний столбец с данными, мы используем аналогичный код

Поиск ячеек с шаблонами

Если вы хотите найти ячейки с определенными шаблонами, вам нужно использовать оператор Like, а не Find.

Например, чтобы найти все имена, начинающиеся с буквы Е, можно использовать следующий код

Если вы хотите узнать об этом больше, ознакомьтесь со статьей Сравнение строк с использованием сопоставления с образцом.

Чтобы увидеть реальный пример использования сопоставления с образцом, ознакомьтесь с Примером 3. Проверка правильности имени файла.

Альтернатива поиску VBA

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

Следующий код считывает значения ячеек в массив, а затем считывает массив для подсчета элементов.

Если вы хотите узнать больше о массивах, ознакомьтесь с публикацией Полное руководство по использованию массивов в Excel VBA.

Найти и заменить

Чтобы найти и заменить, вы можете использовать функцию замены. Это очень похоже на использование функции поиска.

Функция замены выходит за рамки этого поста, хотя многое из того, что вы здесь прочитали, можно использовать с ней. Вы можете увидеть подробности об этом в Microsoft — функция замены VBA

Что дальше?

Бесплатный учебник по VBA Если вы новичок в VBA или хотите отточить свои навыки работы с VBA, почему бы не попробовать The Ultimate VBA Tutorial.

Соответствующее обучение: получите полный доступ к обучающим веб-семинарам Excel VBA и ко всем учебным пособиям.

(ПРИМЕЧАНИЕ. Планируете создать приложение VBA или управлять им? Узнайте, как создать 10 приложений Excel VBA с нуля.)

Здравствуйте, Пол!
Ваши знания мне очень помогли, и вы всегда первым обращаетесь к ресурсу для меня.

Функция fnBonus(empcd)
fnBonus = Range(“tbBonus[Empcode]”).Find(empcd).Offset(0, 1).Value
Конечная функция

Приведенный выше код не берет первую запись из следующей таблицы
Empcode Bonus
1 2900
2 9500
3 2000
4 600
5 4300
6 4200
7 3500
8 1000
9 2700
10 5900

итак, fnBonus(1) дает результат 5900

Есть идеи, что мне здесь не хватает.

Любите свою работу, продолжайте в том же духе.

было бы неплохо обсудить таблицы. Поиск для таблиц возвращает не диапазон, а значение ячеек

Заинтересованы в разработке решений, расширяющих возможности Office на различных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любые технологии веб-программирования, такие как HTML5, JavaScript, CSS3 и XML.

Синтаксис

выражение. Найти (Что, После, Посмотреть, Посмотреть, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

выражение Переменная, представляющая объект Range.

Параметры

Имя	Обязательный/Необязательный	Тип данных	Описание
Что	Обязательно	Вариант	Данные для поиска. Может быть строкой или любым типом данных Microsoft Excel.
После	Необязательно	Вариант	Ячейка, после которой вы хотите, чтобы поиск начался. Это соответствует положению активной ячейки при выполнении поиска из пользовательского интерфейса. Обратите внимание, что После должен быть одной ячейкой в диапазоне. Помните, что поиск начинается после этой ячейки; поиск в указанной ячейке не выполняется до тех пор, пока метод не вернется к этой ячейке. Возвращаемое значение Объект Range, представляющий первую ячейку, в которой найдена эта информация. Примечания Этот метод ничего не возвращает, если совпадений не найдено. Метод Find не влияет на выделение или активную ячейку. Настройки для LookIn, LookAt, SearchOrder и MatchByte сохраняются каждый раз, когда вы используете этот метод. . Если вы не укажете значения для этих аргументов при следующем вызове метода, будут использоваться сохраненные значения. Установка этих аргументов изменяет настройки в диалоговом окне «Найти», а изменение настроек в диалоговом окне «Найти» изменяет сохраненные значения, которые используются, если аргументы опущены. Во избежание проблем задавайте эти аргументы явно при каждом использовании этого метода. Используйте методы FindNext и FindPrevious, чтобы повторить поиск. Когда поиск достигает конца указанного диапазона поиска, он переходит к началу диапазона. Чтобы остановить поиск при возникновении такого циклического перехода, сохраните адрес первой найденной ячейки, а затем сравните каждый последующий адрес найденной ячейки с этим сохраненным адресом. Чтобы найти ячейки, соответствующие более сложным шаблонам, используйте параметр "Для каждого". Следующая инструкция с оператором Like. Например, следующий код ищет все ячейки в диапазоне A1:C5, в которых используется шрифт, имя которого начинается с букв Cour. Когда Microsoft Excel находит совпадение, он меняет шрифт на Times New Roman. Примеры В этом примере выполняется поиск всех ячеек в диапазоне A1:A500 на первом листе, содержащих значение 2, и изменение всего значения ячейки на 5. То есть значения 1234 и 99299 содержат 2, и оба значения ячеек станут равными 5. . В этом примере выполняется поиск всех ячеек в диапазоне A1:A500 на первом листе, которые содержат подстроку "abc", а затем "abc" заменяется на "xyz". Поддержка и обратная связь Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв. Для создания инструментов автоматизации в Excel требуется несколько функций сопоставления строк, таких как Instr() , CStr() , Split() и т. д. Эти функции находят подстроку в строке. Однако при работе со строками/значениями через столбцы/столбцы вы не можете использовать эти функции, так как это однострочные функции. В этой статье будут продемонстрированы три метода поиска строки или значения в столбце. Возвращаемое значение — это номер строки, в которой находится целевая строка. Использование функции Find() Использование функции сопоставления Использование циклов Для всей статьи этот виртуальный лист будет справочным листом. Найти строку/значение в столбце с помощью функции Find() в VBA В приведенном ниже блоке кода показано использование функции Find() для возврата строки строки для поиска. Найти строку/значение в столбце с помощью функции Match() в VBA Основное различие между функциями Find() и Match() заключается в том, что первая возвращает объект Range, в котором найдена строка, а вторая возвращает позицию, в которой строка имеет совпадение. В приведенном ниже блоке кода показано использование функции Match() для возврата строки строки для поиска. Найти строку/значение в столбце с помощью циклов в VBA В отличие от функций Find() и Match(), использование циклов позволяет пользователю выполнить код. Одним из преимуществ циклов является то, что мы можем вернуть все совпадения из диапазона. Однако циклы не рекомендуются при работе с большими данными, поскольку они повторяются с каждой ячейкой в заданных пределах. В приведенном ниже блоке кода показано использование циклов для возврата строки строки для поиска. Поиск строки в нескольких столбцах с помощью циклов в VBA Приведенные выше три блока кода предназначены для просмотра только одного столбца. В редких случаях вам нужно искать совпадения строк в нескольких столбцах или даже на всем листе. Но если вам нужно, приведенный ниже блок кода поможет вам. Статьи DelftStack написаны такими же фанатами программного обеспечения, как и вы. Если вы также хотите внести свой вклад в DelftStack, написав платные статьи, вы можете посетить страницу «Написать для нас». сообщить об этом объявлении Найти — очень полезная и мощная функция Excel. Вместе с функцией Offset вы также можете изменить ячейки вокруг найденной ячейки. Ниже приведены несколько основных примеров, которые вы можете использовать в своем коде. Используйте «Найти», чтобы выбрать ячейку В приведенных ниже примерах выполняется поиск в столбце A листа с именем "Sheet1" для значения поля ввода.Измените имя листа или диапазон в коде на свой лист/диапазон. Совет. Вы можете заменить поле ввода строкой или ссылкой на ячейку следующим образом: FindString = "SearchWord" Или FindString = Sheets("Sheet1").Range( "D1").Значение В этом примере будет выбрана первая ячейка в диапазоне со значением InputBox. Если у вас есть более одного вхождения значения, будет выбрано последнее вхождение. Если у вас есть даты в столбце A, в этом примере будет выбрана ячейка с сегодняшней датой. Примечание. Если ваши даты являются формулами, возможно, вы должны изменить xlFormulas на xlValues в приведенном ниже примере. Если ваши даты являются значениями, xlValues не всегда работает с некоторыми форматами дат. Отметить ячейки с одинаковым значением в столбце A в столбце B В этом примере выполняется поиск в Sheets("Sheet1") в столбце A каждой ячейки со словом "ron" и используется параметр "Смещение", чтобы отметить ячейку в столбце справа. Примечание: вы можете добавить больше значений в массив MyArr. Окрашивание ячеек с одинаковым значением в диапазоне, на листе или на всех листах В этом примере все ячейки в диапазоне Sheets("Sheet1").Range("B1:D100") окрашиваются в "ron". См. комментарии в коде, если вы хотите использовать все ячейки на листе. В этом примере я использую индекс цвета, чтобы присвоить всем ячейкам с "ron" цвет 3 (обычно это красный цвет) Совет. Для изменения цвета шрифта см. примеры строк под макросами. Пример для всех рабочих листов в книге Изменить цвет шрифта вместо внутреннего цвета 'Изменить цвет заливки на "без заливки" во всех ячейках .Interior.ColorIndex = xlColorIndexNone 'Изменить шрифт в столбце на автоматический .Font.ColorIndex = 0 Rng.Interior.ColorIndex = myColor(I) С Rng.Font.ColorIndex = myColor(I) Копировать ячейки на другой лист с помощью команды «Найти» В приведенном ниже примере будут скопированы все ячейки с адресом электронной почты из диапазона Sheets("Sheet1").Range("A1:E100") на новый лист в вашей книге. Примечание. Я использую в коде xlPart вместо xlWhole, чтобы найти каждую ячейку с символом @. Если вы хотите заменить только значения на листе, вы можете использовать Заменить вручную (Ctrl+h) или использовать Заменить в VBA. Приведенный ниже код заменяет ron на dave во всем листе. Измените xlPart на xlWhole, если вы хотите заменить ячейки только на ron. Читайте также: Программное обеспечение для защиты информации на компьютере Как переместить текст вверх или вниз в Word Как вырезать изображение в фотошопе и наложить одно на другое Типы данных Visual Studio Nero 8 micro что это за программа Контакты Политика конфиденциальности

Имя

Обязательный/Необязательный

Тип данных

Описание

Что

Обязательно

Вариант

Данные для поиска. Может быть строкой или любым типом данных Microsoft Excel.

После

Необязательно

Вариант

Ячейка, после которой вы хотите, чтобы поиск начался. Это соответствует положению активной ячейки при выполнении поиска из пользовательского интерфейса.

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

Возвращаемое значение

Объект Range, представляющий первую ячейку, в которой найдена эта информация.

Примечания

Этот метод ничего не возвращает, если совпадений не найдено. Метод Find не влияет на выделение или активную ячейку.

Настройки для LookIn, LookAt, SearchOrder и MatchByte сохраняются каждый раз, когда вы используете этот метод. . Если вы не укажете значения для этих аргументов при следующем вызове метода, будут использоваться сохраненные значения. Установка этих аргументов изменяет настройки в диалоговом окне «Найти», а изменение настроек в диалоговом окне «Найти» изменяет сохраненные значения, которые используются, если аргументы опущены. Во избежание проблем задавайте эти аргументы явно при каждом использовании этого метода.

Используйте методы FindNext и FindPrevious, чтобы повторить поиск.

Когда поиск достигает конца указанного диапазона поиска, он переходит к началу диапазона. Чтобы остановить поиск при возникновении такого циклического перехода, сохраните адрес первой найденной ячейки, а затем сравните каждый последующий адрес найденной ячейки с этим сохраненным адресом.

Чтобы найти ячейки, соответствующие более сложным шаблонам, используйте параметр "Для каждого". Следующая инструкция с оператором Like. Например, следующий код ищет все ячейки в диапазоне A1:C5, в которых используется шрифт, имя которого начинается с букв Cour. Когда Microsoft Excel находит совпадение, он меняет шрифт на Times New Roman.

Примеры

В этом примере выполняется поиск всех ячеек в диапазоне A1:A500 на первом листе, содержащих значение 2, и изменение всего значения ячейки на 5. То есть значения 1234 и 99299 содержат 2, и оба значения ячеек станут равными 5. .

В этом примере выполняется поиск всех ячеек в диапазоне A1:A500 на первом листе, которые содержат подстроку "abc", а затем "abc" заменяется на "xyz".

Поддержка и обратная связь

Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.

Для создания инструментов автоматизации в Excel требуется несколько функций сопоставления строк, таких как Instr() , CStr() , Split() и т. д. Эти функции находят подстроку в строке. Однако при работе со строками/значениями через столбцы/столбцы вы не можете использовать эти функции, так как это однострочные функции.

В этой статье будут продемонстрированы три метода поиска строки или значения в столбце. Возвращаемое значение — это номер строки, в которой находится целевая строка.

Использование функции Find()
Использование функции сопоставления
Использование циклов

Для всей статьи этот виртуальный лист будет справочным листом.

Найти строку/значение в столбце с помощью функции Find() в VBA

В приведенном ниже блоке кода показано использование функции Find() для возврата строки строки для поиска.

Найти строку/значение в столбце с помощью функции Match() в VBA

Основное различие между функциями Find() и Match() заключается в том, что первая возвращает объект Range, в котором найдена строка, а вторая возвращает позицию, в которой строка имеет совпадение.

В приведенном ниже блоке кода показано использование функции Match() для возврата строки строки для поиска.

Найти строку/значение в столбце с помощью циклов в VBA

В отличие от функций Find() и Match(), использование циклов позволяет пользователю выполнить код. Одним из преимуществ циклов является то, что мы можем вернуть все совпадения из диапазона. Однако циклы не рекомендуются при работе с большими данными, поскольку они повторяются с каждой ячейкой в заданных пределах.

В приведенном ниже блоке кода показано использование циклов для возврата строки строки для поиска.

Поиск строки в нескольких столбцах с помощью циклов в VBA

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

Статьи DelftStack написаны такими же фанатами программного обеспечения, как и вы. Если вы также хотите внести свой вклад в DelftStack, написав платные статьи, вы можете посетить страницу «Написать для нас».

сообщить об этом объявлении

Найти — очень полезная и мощная функция Excel. Вместе с функцией Offset вы также можете изменить ячейки вокруг найденной ячейки. Ниже приведены несколько основных примеров, которые вы можете использовать в своем коде.

Используйте «Найти», чтобы выбрать ячейку

В приведенных ниже примерах выполняется поиск в столбце A листа с именем "Sheet1" для значения поля ввода.Измените имя листа или диапазон в коде на свой лист/диапазон.

Совет. Вы можете заменить поле ввода строкой или ссылкой на ячейку следующим образом:
FindString = "SearchWord"
Или
FindString = Sheets("Sheet1").Range( "D1").Значение

В этом примере будет выбрана первая ячейка в диапазоне со значением InputBox.

Если у вас есть более одного вхождения значения, будет выбрано последнее вхождение.

Если у вас есть даты в столбце A, в этом примере будет выбрана ячейка с сегодняшней датой. Примечание. Если ваши даты являются формулами, возможно, вы должны изменить xlFormulas на xlValues в приведенном ниже примере. Если ваши даты являются значениями, xlValues не всегда работает с некоторыми форматами дат.

Отметить ячейки с одинаковым значением в столбце A в столбце B

В этом примере выполняется поиск в Sheets("Sheet1") в столбце A каждой ячейки со словом "ron" и используется параметр "Смещение", чтобы отметить ячейку в столбце справа. Примечание: вы можете добавить больше значений в массив MyArr.

Окрашивание ячеек с одинаковым значением в диапазоне, на листе или на всех листах

В этом примере все ячейки в диапазоне Sheets("Sheet1").Range("B1:D100") окрашиваются в "ron". См. комментарии в коде, если вы хотите использовать все ячейки на листе. В этом примере я использую индекс цвета, чтобы присвоить всем ячейкам с "ron" цвет 3 (обычно это красный цвет)

Совет. Для изменения цвета шрифта см. примеры строк под макросами.

Пример для всех рабочих листов в книге

Изменить цвет шрифта вместо внутреннего цвета

'Изменить цвет заливки на "без заливки" во всех ячейках
.Interior.ColorIndex = xlColorIndexNone

'Изменить шрифт в столбце на автоматический
.Font.ColorIndex = 0

Rng.Interior.ColorIndex = myColor(I)
С
Rng.Font.ColorIndex = myColor(I)

Копировать ячейки на другой лист с помощью команды «Найти»

В приведенном ниже примере будут скопированы все ячейки с адресом электронной почты из диапазона Sheets("Sheet1").Range("A1:E100") на новый лист в вашей книге. Примечание. Я использую в коде xlPart вместо xlWhole, чтобы найти каждую ячейку с символом @.

Если вы хотите заменить только значения на листе, вы можете использовать Заменить вручную (Ctrl+h) или использовать Заменить в VBA. Приведенный ниже код заменяет ron на dave во всем листе. Измените xlPart на xlWhole, если вы хотите заменить ячейки только на ron.

Читайте также: