В этом руководстве будет показано, как использовать методы поиска и замены в Excel VBA.
VBA Найти
Excel имеет отличный встроенный Находить а также Найти и заменить инструменты.
Их можно активировать с помощью ярлыков CTRL + F (Найти) или CTRL + H (Заменить) или через ленту: На главную> Редактирование> Найти и выбрать.
Кликнув Параметры, вы можете увидеть расширенные параметры поиска:
Вы можете легко получить доступ к методам поиска и замены с помощью VBA. Эти встроенные методы намного быстрее, чем все, что вы могли бы написать самостоятельно на VBA.
Найти пример VBA
Чтобы продемонстрировать функциональность поиска, мы создали следующий набор данных в Sheet1.
Если вы хотите продолжить, введите данные в свою рабочую книгу.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
Поиск VBA без дополнительных параметров
При использовании метода поиска VBA вы можете установить множество необязательных параметров.
Мы настоятельно рекомендуем определять все параметры при использовании метода поиска!
Если вы не укажете дополнительные параметры, VBA будет использовать параметры, выбранные в данный момент в окне поиска Excel. Это означает, что вы можете не знать, какие параметры поиска используются при запуске кода. Найти можно было выполнить на всей книге или на листе. Он мог искать формулы или значения. Невозможно узнать, если вы вручную не проверите, что в данный момент выбрано в окне поиска Excel.
Для простоты мы начнем с примера без определенных дополнительных параметров.
Пример простого поиска
Давайте посмотрим на простой пример поиска:
| 123456789 | Sub TestFind ()Dim MyRange As RangeУстановите MyRange = Sheets ("Sheet1"). UsedRange.Find ("employee")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowКонец подписки |
Этот код выполняет поиск «сотрудника» в используемом диапазоне Sheet1. Если он находит «employee», он присваивает первый найденный диапазон переменной диапазона MyRange.
Затем будут отображаться окна сообщений с адресом, столбцом и строкой найденного текста.
В этом примере используются настройки поиска по умолчанию (при условии, что они не были изменены в окне поиска Excel):
- Текст поиска частично совпадает со значением ячейки (точное совпадение ячейки не требуется)
- При поиске регистр не учитывается.
- Искать только на одном листе
Эти настройки можно изменить с помощью различных дополнительных параметров (обсуждаемых ниже).
Примечания к методу поиска
- При поиске не выделяется ячейка, в которой находится текст. Он только определяет найденный диапазон, которым вы можете манипулировать в своем коде.
- Метод Find найдет только первый найденный экземпляр.
- Вы можете использовать подстановочные знаки (*), например. искать "E *"
Ничего не найдено
Если поисковый текст не существует, объект диапазона останется пустым. Это вызывает серьезную проблему, когда ваш код пытается отобразить значения местоположения, поскольку они не существуют. Это приведет к появлению нежелательного сообщения об ошибке.
К счастью, вы можете проверить наличие пустого объекта диапазона в VBA с помощью оператора Is:
| 1 | Если не MyRange - это ничто, тогда |
Добавляем код в наш предыдущий пример:
| 12345678910111213 | Sub TestFind ()Dim MyRange As RangeУстановите MyRange = Sheets ("Sheet1"). UsedRange.Find ("employee")Если не MyRange - это ничто, тогдаMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowЕщеMsgBox «Не найдено»Конец, еслиКонец подписки |
Найти параметры
Пока что мы рассмотрели только базовый пример использования метода Find. Однако существует ряд дополнительных параметров, которые помогут вам уточнить поиск.
| Параметр | Тип | Описание | Ценности |
| Какие | Необходимый | Значение для поиска | Любой тип данных, например строковый или числовой. |
| После | По желанию | Ссылка на одну ячейку, чтобы начать поиск | Сотовый адрес |
| Смотреть в | По желанию | Используйте формулы, значения, комментарии для поиска | xlValues, xlFormulas, xlComments |
| Смотреть на | По желанию | Совпадение части или всей ячейки | xlWhole, xlPart |
| SearchOrder | По желанию | Порядок поиска - строки или столбцы | xlByRows, xlByColummns |
| SearchDirection | По желанию | Направление поиска - вперед или назад | xlNext, xlPrevious |
| Учитывать регистр | По желанию | Поиск чувствителен к регистру или нет | Правда или ложь |
| MatchByte | По желанию | Используется только в том случае, если вы установили поддержку двухбайтового языка, например. китайский язык | Правда или ложь |
| SearchFormat | По желанию | Разрешить поиск по формату ячейки | Правда или ложь |
После параметра и поиска нескольких значений
Вы используете После параметра чтобы указать начальную ячейку для поиска. Это полезно, когда вы ищете более одного экземпляра значения.
Если поиск уже нашел одно значение, и вы знаете, что будет найдено больше значений, тогда вы используете метод Find с параметром «After» для записи первого экземпляра, а затем используете эту ячейку в качестве отправной точки для следующего поиска.
Вы можете использовать это, чтобы найти несколько экземпляров вашего поискового текста:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Dim MyRange как диапазон, OldRange как диапазон, FindStr как строка'Найдите первый экземпляр "Light & Heat"Установите MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")'Если не найден, то выйдитеЕсли MyRange ничего не значит, выйдите из Sub'Показать первый найденный адресMsgBox MyRange.Address'Сделать копию объекта диапазонаУстановите OldRange = MyRange'Добавьте адрес к строке, разделив ее знаком "|" персонажFindStr = FindStr & "|" & MyRange.Address'Перебираем диапазон в поисках других экземпляровДелать'Выполните поиск "Light & Heat", используя предыдущий найденный адрес в качестве параметра "После".Установить MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Если адрес уже был найден, выйдите из цикла do - это остановит непрерывный циклЕсли InStr (FindStr, MyRange.Address), то выйти из Do'Показать последний найденный адресMsgBox MyRange.Address'Добавить последний адрес в строку адресовFindStr = FindStr & "|" & MyRange.Address'сделать копию текущего диапазонаУстановите OldRange = MyRangeПетляКонец подписки |
Этот код будет перебирать используемый диапазон и отображать адрес каждый раз, когда находит экземпляр «Light & Heat».
Обратите внимание, что код будет продолжать цикл до тех пор, пока в FindStr не будет найден повторяющийся адрес, и в этом случае он выйдет из цикла Do.
Параметр LookIn
Вы можете использовать Параметр LookIn , чтобы указать, в каком компоненте ячейки вы хотите выполнять поиск. В ячейке можно указать значения, формулы или комментарии.
- xlValues - Ищет значения ячеек (последнее значение ячейки после вычисления)
- xlFormulas - Поиск внутри самой формулы ячейки (независимо от того, что введено в ячейку)
- xlComments - Поиск в примечаниях к ячейкам
- xlCommentsThreaded - Поиск в комментариях к ячейкам
Предполагая, что формула была введена на лист, вы можете использовать этот пример кода, чтобы найти первое местоположение любой формулы:
| 12345678910 | Sub TestLookIn ()Dim MyRange As RangeУстановить MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Если не MyRange - это ничто, тогдаMsgBox MyRange.AddressЕщеMsgBox «Не найдено»Конец, еслиКонец подписки |
Если для параметра LookIn задано значение xlValues, код отобразит сообщение «Not Found». В этом примере он вернет B10.
Использование параметра LookAt
В Параметр LookAt определяет, будет ли find искать точное совпадение ячейки или искать любую ячейку, содержащую значение поиска.
- xlWhole - Требуется, чтобы вся ячейка соответствовала поисковому значению.
- xlPart - Ищет в ячейке строку поиска
В этом примере кода будет найдена первая ячейка, содержащая текст «свет». С участием Lookat: = xlPart, он вернет совпадение для «Light & Heat».
| 123456789 | Sub TestLookAt ()Dim MyRange As RangeУстановить MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Если не MyRange - это ничто, тогдаMsgBox MyRange.AddressЕщеMsgBox «Не найдено»Конец, еслиКонец подписки |
Если xlWhole было установлено, совпадение вернется только в том случае, если значение ячейки было «светлым».
Параметр SearchOrder
В Параметр SearchOrder определяет, как будет осуществляться поиск по всему диапазону.
- xlRows - Поиск ведется построчно
- xlXolumns - Поиск осуществляется столбец за столбцом
| 123456789 | Sub TestSearchOrder ()Dim MyRange As RangeУстановите MyRange = Sheets ("Sheet1"). UsedRange.Find ("employee", SearchOrder: = xlColumns)Если не MyRange - это ничто, тогдаMsgBox MyRange.AddressЕщеMsgBox «Не найдено»Конец, еслиКонец подписки |
Это влияет на то, какое совпадение будет найдено первым.
Используя тестовые данные, введенные в рабочий лист ранее, когда порядок поиска - столбцы, обнаруженная ячейка - A5. Когда параметр порядка поиска изменяется на xlRows, обнаруженная ячейка - C4
Это важно, если у вас есть повторяющиеся значения в пределах диапазона поиска и вы хотите найти первый экземпляр под определенным именем столбца.
Параметр SearchDirection
В Параметр SearchDirection определяет, в каком направлении будет идти поиск - вперед или назад.
- xlNext - Поиск следующего совпадающего значения в диапазоне
- xlPrevious - Поиск предыдущего совпадающего значения в диапазоне
Опять же, если в диапазоне поиска есть повторяющиеся значения, это может повлиять на то, какое из них будет найдено первым.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange As RangeУстановите MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Если не MyRange - это ничто, тогдаMsgBox MyRange.AddressЕщеMsgBox «Не найдено»Конец, еслиКонец подписки |
Используя этот код на тестовых данных, направление поиска xlPrevious вернет местоположение C9. Использование параметра xlNext вернет местоположение A4.
Параметр Next означает, что поиск начнется в верхнем левом углу диапазона поиска и будет идти вниз. Параметр Previous означает, что поиск начнется в правом нижнем углу диапазона поиска и будет двигаться вверх.
Параметр MatchByte
В Параметр MatchBye используется только для языков, в которых для представления каждого символа используется двухбайтный символ, например для китайского, русского и японского.
Если для этого параметра установлено значение «True», тогда функция Find будет сопоставлять только двухбайтовые символы с двухбайтовыми символами. Если для параметра установлено значение «False», то двухбайтовый символ будет соответствовать одно- или двухбайтовым символам.
Параметр SearchFormat
В Параметр SearchFormat позволяет искать подходящие форматы ячеек. Это может быть конкретный используемый шрифт, жирный шрифт или цвет текста. Прежде чем использовать этот параметр, вы должны установить формат, необходимый для поиска, с помощью свойства Application.FindFormat.
Вот пример того, как его использовать:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueУстановите MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Если не MyRange - это ничто, тогдаMsgBox MyRange.AddressЕщеMsgBox «Не найдено»Конец, еслиApplication.FindFormat.ClearКонец подписки |
В этом примере FindFormat свойство настроено на поиск жирного шрифта. Затем оператор Find выполняет поиск слова «heat», устанавливая для параметра SearchFormat значение True, чтобы он возвращал экземпляр этого текста только в том случае, если шрифт выделен жирным шрифтом.
В примере данных рабочего листа, показанном ранее, это вернет A9, единственную ячейку, содержащую слово «тепло», выделенное жирным шрифтом.
Убедитесь, что свойство FindFormat очищено в конце кода. Если вы этого не сделаете, ваш следующий поиск все равно будет учитывать это и возвращать неверные результаты.
Если вы используете параметр SearchFormat, вы также можете использовать подстановочный знак (*) в качестве значения поиска. В этом случае он будет искать любое значение, выделенное жирным шрифтом:
| 1 | Установить MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Использование нескольких параметров
Все обсуждаемые здесь параметры поиска при необходимости могут использоваться в сочетании друг с другом.
Например, вы можете объединить параметр LookIn с параметром MatchCase, чтобы просматривать весь текст ячейки, но с учетом регистра.
| 123456789 | Sub TestMultipleParameters ()Dim MyRange As RangeУстановите MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Если не MyRange - это ничто, тогдаMsgBox MyRange.AddressЕщеMsgBox «Не найдено»Конец, еслиКонец подписки |
В этом примере код вернет A4, но если бы мы использовали только часть текста, например. ‘Heat’, ничего не будет найдено, потому что мы сопоставляем все значение ячейки. Кроме того, это не удастся из-за несоответствия регистра.
| 1 | Установите MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Заменить в Excel VBA
Как и следовало ожидать, в Excel VBA есть функция замены, которая работает аналогично функции «Найти», но заменяет значения в найденной ячейке новым значением.
Это параметры, которые вы можете использовать в инструкции метода Replace. Они работают точно так же, как и оператор Find method. Единственное отличие от «Найти» состоит в том, что вам нужно указать параметр замены.
| Имя | Тип | Описание | Ценности |
| Какие | Необходимый | Значение для поиска | Любой тип данных, например строковый или числовой. |
| Замена | Необходимый | Строка замены. | Любой тип данных, например строковый или числовой. |
| Смотреть на | По желанию | Совпадение части или всей ячейки | xlPart или xlWhole |
| SearchOrder | По желанию | Порядок поиска - строки или столбцы | xlByRows или xlByColumns |
| Учитывать регистр | По желанию | Поиск чувствителен к регистру или нет | Правда или ложь |
| MatchByte | По желанию | Используется только в том случае, если вы установили поддержку двухбайтового языка | Правда или ложь |
| SearchFormat | По желанию | Разрешить поиск по формату ячейки | Правда или ложь |
| ReplaceFormat | По желанию | Формат замены для метода. | Правда или ложь |
Параметр Replace Format выполняет поиск ячейки с определенным форматом, например. жирным шрифтом точно так же, как параметр SearchFormat работает в методе Find. Сначала необходимо установить свойство Application.FindFormat, как показано в примере кода Find, показанном ранее.
Заменить без дополнительных параметров
В самом простом случае вам нужно только указать, что вы ищете и чем вы хотите это заменить.
| 123 | Sub TestReplace ()Листы ("Sheet1"). UsedRange.Replace What: = "Light & Heat", Replacement: = "L & H"Конец подписки |
Обратите внимание, что метод Find вернет только первый экземпляр совпадающего значения, тогда как метод Replace работает во всем указанном диапазоне и заменяет все, в чем он находит совпадение.
Показанный здесь код замены заменит каждый экземпляр «Light & Heat» на «L & H» во всем диапазоне ячеек, определенных объектом UsedRange.
Использование VBA для поиска или замены текста в текстовой строке VBA
Приведенные выше примеры отлично работают при использовании VBA для взаимодействия с данными Excel. Однако для взаимодействия со строками VBA вы можете использовать встроенные функции VBA, такие как INSTR и REPLACE.
Вы можете использовать Функция INSTR для поиска строки текста в более длинной строке.
| 123 | Sub TestInstr ()MsgBox InStr («Это строка MyText», «MyText»)Конец подписки |
Этот пример кода вернет значение 9, которое является позицией числа, в котором «MyText» находится в строке для поиска.
Обратите внимание, что это чувствительно к регистру. Если «MyText» написан в нижнем регистре, то будет возвращено значение 0, что означает, что строка поиска не найдена. Ниже мы обсудим, как отключить чувствительность к регистру.
INSTR - Начать
Доступны еще два дополнительных параметра. Вы можете указать начальную точку поиска:
| 1 | MsgBox InStr (9, «Это строка MyText», «MyText») |
Начальная точка указана как 9, поэтому она все равно вернет 9. Если начальная точка была 10, тогда она вернет 0 (нет совпадения), поскольку начальная точка будет слишком далеко вперед.
INSTR - чувствительность к регистру
Вы также можете установить для параметра сравнения значение vbBinaryCompare или vbTextCompare. Если вы устанавливаете этот параметр, инструкция должна иметь начальное значение параметра.
- vbBinaryCompare - С учетом регистра (по умолчанию)
- vbTextCompare - Нечувствительный к регистру
| 1 | MsgBox InStr (1, «Это строка MyText», «mytext», vbTextCompare) |
Этот оператор все равно вернет 9, даже если поисковый текст будет в нижнем регистре.
Чтобы отключить чувствительность к регистру, вы также можете объявить Option Compare Text в верхней части модуля кода.
Функция замены VBA
Если вы хотите заменить символы в строке другим текстом в вашем коде, тогда метод Replace идеально подходит для этого:
| 123 | Sub TestReplace ()MsgBox Replace («Это строка MyText», «MyText», «Мой текст»)Конец подписки |
Этот код заменяет MyText на My Text. Обратите внимание, что строка поиска чувствительна к регистру, поскольку по умолчанию используется двоичное сравнение.
Вы также можете добавить другие необязательные параметры:
- Начинать - определяет позицию в начальной строке, с которой должна начинаться замена. В отличие от метода Find, он возвращает усеченную строку, начиная с номера символа, определенного параметром Start.
- Считать - определяет количество производимых замен. По умолчанию Replace изменит каждый найденный экземпляр найденного текста, но вы можете ограничить это одной заменой, установив для параметра Count значение 1.
- Сравнивать - как и в методе Find, вы можете указать двоичный поиск или текстовый поиск, используя vbBinaryCompare или vbTextCompare. В двоичном формате учитывается регистр, а в тексте регистр не учитывается.
| 1 | MsgBox Replace («Это строка MyText (mytext)», «MyText», «Мой текст», 9, 1, vbTextCompare) |
Этот код возвращает «Моя текстовая строка (mytext)». Это связано с тем, что задана начальная точка 9, поэтому новая возвращаемая строка начинается с символа 9. Был изменен только первый «MyText», поскольку для параметра Count установлено значение 1.
Метод Replace идеально подходит для решения таких проблем, как имена людей, содержащие апострофы, например О’Флинн. Если вы используете одинарные кавычки для определения строкового значения и есть апостроф, это вызовет ошибку, потому что код интерпретирует апостроф как конец строки и не распознает оставшуюся часть строки.
Вы можете использовать метод Replace, чтобы заменить апостроф ничем, полностью удалив его.
