Цей підручник покаже, як використовувати методи пошуку та заміни у програмі Excel VBA.
Знайти VBA
Excel має відмінну вбудовану функцію Знайти та Знайти та замінити інструменти.
Їх можна активувати за допомогою ярликів CTRL + F (Знайти) або CTRL + H (Замінити) або через стрічку: Домашня сторінка> Редагування> Знайти та вибрати.
Натиснувши Варіанти, Ви можете побачити розширені параметри пошуку:
Ви можете легко отримати доступ до методів пошуку та заміни за допомогою VBA. Ці вбудовані методи набагато швидші, ніж будь-що, що ви можете написати самостійно у VBA.
Знайдіть приклад VBA
Щоб продемонструвати функціональність Find, ми створили наступний набір даних у Sheet1.
Якщо ви хочете продовжити, введіть дані у власну книгу.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA Find без додаткових параметрів
При використанні методу пошуку VBA можна задати багато додаткових параметрів.
Ми настійно рекомендуємо визначати всі параметри кожного разу, коли використовується метод пошуку!
Якщо ви не визначаєте додаткові параметри, VBA використовуватиме вибрані наразі параметри у вікні пошуку Excel. Це означає, що ви можете не знати, які параметри пошуку використовуються під час запуску коду. Знайти можна запустити по всій книзі або на аркуші. Він може шукати формули або значення. Це неможливо дізнатись, якщо ви не перевірите вручну те, що наразі вибрано у вікні пошуку Excel.
Для простоти ми почнемо з прикладу без визначених додаткових параметрів.
Приклад простого пошуку
Давайте розглянемо простий приклад Find:
| 123456789 | Sub TestFind ()Dim MyRange As RangeВстановити MyRange = Sheets ("Sheet1"). UsedRange.Find ("співробітник")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowEnd Sub |
Цей код шукає "працівника" у вживаному діапазоні аркуша1. Якщо він знайде “працівника”, він призначить перший знайдений діапазон змінній діапазону MyRange.
Далі відображатимуться поля повідомлень з адресою, стовпцем і рядком знайденого тексту.
У цьому прикладі використовуються параметри пошуку за умовчанням (за умови, що вони не були змінені у вікні пошуку Excel):
- Текст пошуку частково відповідає значенню клітинки (точна відповідність комірки не потрібна)
- Пошук не враховує регістр.
- Функція пошуку здійснює пошук лише по одному аркушу
Ці налаштування можна змінювати за допомогою різних додаткових параметрів (розглянуті нижче).
Знайдіть примітки до методів
- Знайти не вибирає клітинку, де знаходиться текст. Він лише визначає знайдений діапазон, яким можна маніпулювати у своєму коді.
- Метод Find знайде лише перший знайдений екземпляр.
- Ви можете використовувати символи підстановки (*), наприклад пошук "E*"
Нічого не знайдено
Якщо текст пошуку не існує, то об'єкт діапазону залишиться порожнім. Це викликає серйозну проблему, коли ваш код намагається відобразити значення розташування, оскільки вони не існують. Це призведе до повідомлення про помилку, яке ви не бажаєте.
На щастя, ви можете перевірити наявність порожнього об'єкта діапазону у VBA за допомогою оператора Is Opera:
| 1 | Якщо ні, то MyRange - це нічого |
Додавання коду до попереднього прикладу:
| 12345678910111213 | Sub TestFind ()Dim MyRange As RangeВстановити MyRange = Sheets ("Sheet1"). UsedRange.Find ("співробітник")Якщо ні, то MyRange - це нічогоMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowІнакшеПовідомлення "Не знайдено"Закінчити ЯкщоEnd Sub |
Знайти параметри
Поки що ми розглянули лише базовий приклад використання методу Find. Однак існує ряд додаткових параметрів, які допоможуть вам уточнити пошук
| Параметр | Тип | Опис | Цінності |
| Що | вимагається | Значення для пошуку | Будь -який тип даних, наприклад рядок або числовий |
| Після | Необов’язково | Посилання на одну клітинку, щоб розпочати пошук | Адреса мобільного телефону |
| Заглядати | Необов’язково | Для пошуку використовуйте формули, значення, коментарі | xlValues, xlFormulas, xlComments |
| Подивись на | Необов’язково | Установіть відповідність між клітиною або її частиною | xlВся, xlЧастина |
| SearchOrder | Необов’язково | Порядок пошуку в рядках або стовпцях | xlByRows, xlByColummns |
| SearchDirection | Необов’язково | Напрямок для пошуку - вперед чи назад | xlНаступний, xlПопередній |
| MatchCase | Необов’язково | Пошук з урахуванням регістру чи ні | Правда чи неправда |
| MatchByte | Необов’язково | Використовується, лише якщо ви встановили підтримку двобайтової мови, наприклад китайська мова | Правда чи неправда |
| Формат пошуку | Необов’язково | Дозволити пошук за форматом комірки | Правда чи неправда |
Після параметра та пошуку кількох значень
Ви використовуєте Після параметра щоб вказати початкову клітинку для вашого пошуку. Це корисно, якщо є більше одного екземпляра значення, яке ви шукаєте.
Якщо пошук вже знайшов одне значення, і ви знаєте, що буде знайдено більше значень, то ви використовуєте метод Find з параметром "Після", щоб записати перший екземпляр, а потім використовувати цю клітинку як відправну точку для наступного пошуку.
Ви можете використовувати це, щоб знайти кілька екземплярів тексту пошуку:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Dim MyRange як діапазон, OldRange як діапазон, FindStr як рядок'Шукайте перший екземпляр "" Світло та тепло "Встановити MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")'Якщо не знайдено, вийдітьЯкщо MyRange - це нічого, вийдіть із Sub'Відображати першу знайдену адресуMsgBox MyRange.Address'Зробіть копію об'єкта діапазонуВстановити OldRange = MyRange'Додайте адресу до рядка, що розмежовує "|" характерFindStr = FindStr & "|" & MyRange.Address«Перебирайте діапазон у пошуках інших випадківЗробити"Шукайте" Світло та тепло ", використовуючи попередньо знайдену адресу як параметр ПісляВстановити 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ПетляEnd Sub |
Цей код буде повторюватись у використаному діапазоні та відображатиме адресу кожного разу, коли знайде екземпляр "Світло та тепло"
Зауважте, що код буде продовжувати цикл, поки в FindStr не знайдеться дублікат адреси, і в цьому випадку він вийде з циклу Do.
Параметр LookIn
Ви можете використовувати Параметр LookIn , щоб вказати, у якому компоненті клітинки ви хочете здійснювати пошук. Ви можете вказати значення, формули або коментарі в комірці.
- xlValues - Шукає значення комірок (остаточне значення комірки після її обчислення)
- xlФормули - Пошук у самій формулі комірки (все, що введено в клітинку)
- xlКоментарі - Пошук у примітках комірки
- xlCommentsThreaded - Пошук у коментарях комірки
Якщо припустити, що на робочому аркуші введена формула, можна скористатися цим прикладом коду, щоб знайти перше розташування будь -якої формули:
| 12345678910 | Sub TestLookIn ()Dim MyRange As RangeВстановити MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Якщо ні, то MyRange - це нічогоMsgBox MyRange.AddressІнакшеПовідомлення "Не знайдено"Закінчити ЯкщоEnd Sub |
Якщо для параметра "LookIn" встановлено значення xlValues, код відображатиме повідомлення "Не знайдено". У цьому прикладі він поверне B10.
Використання параметра LookAt
The Параметр LookAt визначає, чи буде пошук шукати точну відповідність комірки, або шукати будь -яку клітинку, що містить значення пошуку.
- xlВся - Потрібна вся клітинка, щоб вона відповідала значенню пошуку
- xlPart - Шукає рядок пошуку в комірці
У цьому прикладі коду буде знайдено першу клітинку, що містить текст «світло». З Переглянути: = xlPart, він поверне матч для "Світла та тепла".
| 123456789 | Sub TestLookAt ()Dim MyRange As RangeВстановити MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Якщо ні, то MyRange - це нічогоMsgBox MyRange.AddressІнакшеПовідомлення "Не знайдено"Закінчити ЯкщоEnd Sub |
Якщо xlВся було встановлено, збіг повернеться, лише якщо значення клітинки буде "світлим".
Параметр SearchOrder
The Параметр SearchOrder визначає, як буде здійснюватися пошук у всьому діапазоні.
- xlRows - Пошук здійснюється по рядках
- xlXolumns - Пошук здійснюється за стовпцем
| 123456789 | Sub TestSearchOrder ()Dim MyRange As RangeВстановити MyRange = Sheets ("Sheet1"). UsedRange.Find ("співробітник", SearchOrder: = xlColumns)Якщо ні, то MyRange - це нічогоMsgBox MyRange.AddressІнакшеПовідомлення "Не знайдено"Закінчити ЯкщоEnd Sub |
Це впливає на те, який збіг буде знайдений першим.
Використовуючи тестові дані, внесені до робочого аркуша раніше, коли порядок пошуку - це стовпці, розташована клітинка A5. Коли параметр порядку пошуку змінюється на xlRows, клітинка, що знаходиться, є C4
Це важливо, якщо у вас є повторювані значення в діапазоні пошуку, і ви хочете знайти перший екземпляр під певною назвою стовпця.
Параметр SearchDirection
The Параметр SearchDirection визначає, у якому напрямку рухатиметься пошук - вперед чи назад.
- xlДалі - Шукайте наступне відповідне значення в діапазоні
- xlПопередній - Шукайте попереднє відповідне значення в діапазоні
Знову ж таки, якщо в діапазоні пошуку є повторювані значення, це може вплинути на те, що одне буде знайдено першим.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange As RangeВстановити MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Якщо ні, то MyRange - це нічогоMsgBox MyRange.AddressІнакшеПовідомлення "Не знайдено"Закінчити ЯкщоEnd Sub |
Використовуючи цей код для тестових даних, напрямок пошуку xlPrevious поверне місце розташування C9. Використання параметра xlNext поверне розташування A4.
Параметр Next означає, що пошук розпочнеться у верхньому лівому куті діапазону пошуку і працюватиме вниз. Параметр Попередній означає, що пошук почнеться в нижньому правому куті діапазону пошуку і працюватиме вгору.
Параметр MatchByte
The Параметр MatchBye використовується лише для мов, які використовують двобайтові символи для представлення кожного символу, наприклад китайської, російської та японської.
Якщо для цього параметра встановлено значення "True", то Find буде відповідати лише двобайтовим символам з двобайтовими символами. Якщо для параметра встановлено значення «False», то двобайтовий символ буде відповідати одно- або двобайтовим символам.
Параметр SearchFormat
The Параметр SearchFormat дозволяє шукати відповідні формати комірок. Це може бути певний використаний шрифт, жирний шрифт або колір тексту. Перш ніж використовувати цей параметр, необхідно встановити формат, необхідний для пошуку, за допомогою властивості Application.FindFormat.
Ось приклад того, як ним користуватися:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = ІстинаВстановити MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Якщо ні, то MyRange - це нічогоMsgBox MyRange.AddressІнакшеПовідомлення "Не знайдено"Закінчити ЯкщоApplication.FindFormat.ClearEnd Sub |
У цьому прикладі FindFormat властивість налаштована на пошук жирного шрифту. Потім оператор Find шукає слово "heat", встановлюючи для параметра SearchFormat значення True, щоб воно повертало лише екземпляр цього тексту, якщо шрифт виділений жирним шрифтом.
У зразках даних робочого аркуша, показаних раніше, це поверне A9, яка є єдиною коміркою, що містить слово «тепло» жирним шрифтом.
Переконайтеся, що властивість FindFormat очищена в кінці коду. Якщо ви цього не зробите, ваш наступний пошук все одно врахує це і поверне неправильні результати.
Якщо ви використовуєте параметр SearchFormat, ви також можете використовувати підстановку (*) як значення пошуку. У цьому випадку він буде шукати будь -яке значення жирним шрифтом:
| 1 | Встановити MyRange = Аркуші ("Лист1"). UsedRange.Find ("*", Формат пошуку: = Істина) |
Використання декількох параметрів
Усі параметри пошуку, обговорювані тут, можна використовувати в поєднанні один з одним, якщо це необхідно.
Наприклад, ви можете поєднати параметр "LookIn" з параметром "MatchCase", щоб переглянути весь текст комірки, але він враховує регістр
| 123456789 | Sub TestMultipleParameters ()Dim MyRange As RangeВстановити MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Якщо ні, то MyRange - це нічогоMsgBox MyRange.AddressІнакшеПовідомлення "Не знайдено"Закінчити ЯкщоEnd Sub |
У цьому прикладі код поверне A4, але якщо ми використали лише частину тексту, напр. "Тепло", нічого не буде знайдено, тому що ми порівнюємо значення клітинки в цілому. Крім того, це не вдасться через те, що справа не відповідає.
| 1 | Встановити MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Замінити в Excel VBA
Як і слід було очікувати, у Excel VBA є функція «Замінити», яка працює дуже подібно до «Знайти», але замінює значення у знайденому місці клітинки на нове значення.
Це параметри, які можна використовувати в операторі методу Замінити. Вони працюють точно так само, як і для інструкції методу Find. Єдина відмінність від "Знайти" полягає в тому, що вам потрібно вказати параметр заміни.
| Назва | Тип | Опис | Цінності |
| Що | вимагається | Значення для пошуку | Будь -який тип даних, наприклад рядок або числовий |
| Заміна | вимагається | Рядок заміни. | Будь -який тип даних, наприклад рядок або числовий |
| Подивись на | Необов’язково | Установіть відповідність між частиною або цілим клітиною | xlPart або xlWhole |
| SearchOrder | Необов’язково | Порядок пошуку в - рядки або стовпці | xlByRows або xlByColumns |
| MatchCase | Необов’язково | Пошук з урахуванням регістру чи ні | Правда чи неправда |
| MatchByte | Необов’язково | Використовується, лише якщо ви встановили підтримку двобайтової мови | Правда чи неправда |
| Формат пошуку | Необов’язково | Дозволити пошук за форматом комірки | Правда чи неправда |
| Замінити формат | Необов’язково | Формат заміни методу. | Правда чи неправда |
Параметр «Замінити формат» шукає клітинку з певним форматом, наприклад жирним шрифтом так само, як параметр SearchFormat працює у методі Find. Потрібно спочатку встановити властивість Application.FindFormat, як показано в прикладі пошуку коду, показаному раніше
Заміна без додаткових параметрів
У найпростішому, вам потрібно лише вказати, що ви шукаєте і чим ви хочете його замінити.
| 123 | Sub TestReplace ()Аркуші ("Аркуш1"). UsedRange.Replace What: = "Світло та тепло", Заміна: = "L & H"End Sub |
Зауважте, що метод Find повертає лише перший екземпляр узгодженого значення, тоді як метод Replace працює у всьому зазначеному діапазоні і замінює все, на що він знаходить відповідність.
Код заміни, показаний тут, замінить кожен екземпляр "Світло та тепло" на "L & H" у всьому діапазоні клітинок, визначених об'єктом UsedRange
Використання VBA для пошуку або заміни тексту в текстовому рядку VBA
Наведені вище приклади чудово працюють при використанні VBA для взаємодії з даними Excel. Однак для взаємодії з рядками VBA можна використовувати вбудовані функції VBA, такі як INSTR та REPLACE.
Ви можете використовувати Функція INSTR щоб знайти рядок тексту в довшому рядку.
| 123 | Sub TestInstr ()MsgBox InStr ("Це рядок MyText", "MyText")End Sub |
Цей приклад коду поверне значення 9, яке є позицією числа, де "MyText" знаходиться в рядку для пошуку.
Зауважте, що він чутливий до регістру. Якщо "Мій текст" - це нижній регістр, то буде повернуто значення 0, що означає, що рядок пошуку не знайдено. Нижче ми обговоримо, як відключити чутливість до регістру.
INSTR - Почати
Доступні ще два додаткових параметри. Ви можете вказати точку початку пошуку:
| 1 | MsgBox InStr (9, "Це рядок MyText", "MyText") |
Початкова точка вказана як 9, тому вона все одно повертатиметься 9. Якщо початкова точка буде 10, то вона поверне 0 (без відповідності), оскільки початкова точка буде занадто далеко вперед.
INSTR - Чутливість до регістру
Ви також можете встановити для параметра порівняння значення vbBinaryCompare або vbTextCompare. Якщо ви встановили цей параметр, оператор повинен мати значення початкового параметра.
- vbBinaryCompare - З урахуванням регістру (за замовчуванням)
- vbTextCompare - Не враховує регістр
| 1 | MsgBox InStr (1, "Це рядок MyText", "мітекст", vbTextCompare) |
Цей вираз все одно повертатиме 9, навіть якщо текст пошуку - нижній регістр.
Щоб вимкнути чутливість до регістру, ви також можете оголосити Option Compare Text у верхній частині модуля коду.
Функція заміни VBA
Якщо ви хочете замінити символи в рядку іншим текстом у вашому коді, то метод Replace ідеально підходить для цього:
| 123 | Sub TestReplace ()MsgBox Replace ("Це рядок MyText", "MyText", "My Text")End Sub |
Цей код замінює "Мій текст" на "Мій текст". Зауважте, що рядок пошуку чутливий до регістру, оскільки за замовчуванням двійкове порівняння.
Ви також можете додати інші додаткові параметри:
- Почати - визначає позицію у початковому рядку, з якої має початися заміна. На відміну від методу Find, він повертає усічений рядок, починаючи з номера символу, визначеного параметром Start.
- Рахувати - визначає кількість замін, які необхідно здійснити. За замовчуванням Replace змінює кожен екземпляр знайденого тексту пошуку, але ви можете обмежити це однією заміною, встановивши для параметра Count значення 1
- Порівняйте - як і в методі Find, ви можете вказати двійковий пошук або текстовий пошук за допомогою vbBinaryCompare або vbTextCompare. Двійковий файл чутливий до регістру, а текст - до регістру
| 1 | MsgBox Replace ("Це рядок MyText (мітекст)", "Мій текст", "Мій текст", 9, 1, vbTextCompare) |
Цей код повертає "Мій текстовий рядок (мітекст)". Це тому, що початкова точка - 9, тому новий повернений рядок починається з символу 9. Змінено лише перший "Мій текст", оскільки для параметра Count встановлено значення 1.
Метод замінити ідеально підходить для вирішення таких проблем, як імена людей, що містять апострофи, наприклад О’Флінн. Якщо ви використовуєте одинарні лапки для визначення значення рядка і є апостроф, це призведе до помилки, оскільки код буде інтерпретувати апостроф як кінець рядка і не розпізнаватиме залишок рядка.
Ви можете використовувати метод Replace, щоб замінити апостроф нічим, повністю видаливши його.
