Този урок ще покаже как да използвате методите Find and Replace в Excel VBA.
VBA Find
Excel има отлично вградено намирам и Намери и замени инструменти.
Те могат да се активират с преките пътища CTRL + F (Намерете) или CTRL + H (Замяна) или през лентата: Начало> Редактиране> Намиране и избор.
Чрез щракване Настроики, можете да видите разширени опции за търсене:
Можете лесно да получите достъп до методите Find and Replace с помощта на VBA. Тези вградени методи са много по-бързи от всичко, което бихте могли да напишете сами във VBA.
Намерете пример за VBA
За да демонстрираме функционалността Find, създадохме следния набор от данни в Sheet1.
Ако искате да следвате, въведете данните в собствената си работна книга.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA Find без незадължителни параметри
Когато използвате метода VBA Find, можете да зададете много незадължителни параметри.
Силно препоръчваме да дефинирате всички параметри винаги, когато използвате метода Find!
Ако не дефинирате незадължителните параметри, VBA ще използва текущо избраните параметри в прозореца Find на Excel. Това означава, че може да не знаете какви параметри за търсене се използват, когато кодът се изпълнява. Find може да се изпълнява върху цялата работна книга или лист. Може да търси формули или стойности. Няма начин да разберете, освен ако не проверите ръчно какво е избрано в момента в прозореца за търсене на Excel.
За простота ще започнем с пример без дефинирани незадължителни параметри.
Пример за просто намиране
Нека разгледаме един прост пример за намиране:
| 123456789 | Sub TestFind ()Dim MyRange As RangeЗадайте MyRange = Sheets ("Sheet1"). UsedRange.Find ("служител")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowEnd Sub |
Този код търси „служител“ в използвания диапазон на лист1. Ако намери „служител“, той ще присвои първия намерен диапазон на променливата на диапазона MyRange.
След това ще се покажат кутии за съобщения с адреса, колоната и реда на намерения текст.
В този пример се използват настройките по подразбиране за намиране (ако приемем, че не са променени в прозореца за търсене на Excel):
- Текстът за търсене е частично съпоставен със стойността на клетката (не се изисква точно съвпадение на клетката)
- Търсенето не е чувствително към регистъра.
- Find само търси в един работен лист
Тези настройки могат да се променят с различни незадължителни параметри (обсъдени по -долу).
Намерете бележки за метода
- Find не избира клетката, в която е намерен текстът. Той само идентифицира намерения диапазон, който можете да манипулирате във вашия код.
- Методът Find ще локализира само първия намерен екземпляр.
- Можете да използвате заместващи знаци (*) напр. потърсете „E*“
Нищо не е намерено
Ако текстът за търсене не съществува, обектът на диапазона ще остане празен. Това причинява голям проблем, когато вашият код се опитва да покаже стойностите на местоположението, защото те не съществуват. Това ще доведе до съобщение за грешка, което не искате.
За щастие можете да тествате за празен обхват в рамките на VBA с помощта на Is Operator:
| 1 | Ако не MyRange е нищо |
Добавяне на кода към предишния ни пример:
| 12345678910111213 | Sub TestFind ()Dim MyRange As RangeЗадайте MyRange = Sheets ("Sheet1"). UsedRange.Find ("служител")Ако не MyRange е нищоMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowИначеMsgBox "Не е намерен"Край АкоEnd Sub |
Намерете параметри
Досега разглеждахме само основен пример за използване на метода Find. Съществуват обаче редица незадължителни параметри, които да ви помогнат да прецизирате търсенето си
| Параметър | Тип | Описание | Стойности |
| Какво | Задължително | Стойността за търсене | Всеки тип данни, като низ или число |
| След | По избор | Единична препратка към клетка, за да започнете търсенето | Адрес на клетката |
| Погледни вътре | По избор | Използвайте формули, стойности, коментари за търсене | xlValues, xlFormulas, xlComments |
| Погледнете | По избор | Свържете част или цяла клетка | xl Цял, xlЧаст |
| SearchOrder | По избор | Редът за търсене в - редове или колони | xlByRows, xlByColummns |
| SearchDirection | По избор | Посока за търсене, която да влезе - напред или назад | xlСледваща, xlПредходна |
| MatchCase | По избор | Търсенето е чувствително към регистъра или не | Истина или лъжа |
| MatchByte | По избор | Използва се само ако сте инсталирали поддръжка на двубайтов език, напр. китайски език | Истина или лъжа |
| Формат за търсене | По избор | Разрешаване на търсене по формат на клетката | Истина или лъжа |
След параметър и намиране на множество стойности
Използвате След параметъра за да посочите началната клетка за вашето търсене. Това е полезно, когато има повече от един екземпляр на стойността, която търсите.
Ако едно търсене вече е намерило една стойност и знаете, че ще бъдат намерени още стойности, тогава използвайте метода Find с параметъра „След“, за да запишете първия екземпляр и след това да използвате тази клетка като отправна точка за следващото търсене.
Можете да използвате това, за да намерите множество копия на текста за търсене:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Dim MyRange като Range, OldRange As Range, FindStr As String„Потърсете първия екземпляр на„ „Светлина и топлина“Задайте MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")„Ако не бъде намерен, излезтеАко MyRange не е нищо, излезте от Sub„Показване на първия намерен адресMsgBox MyRange.Address„Направете копие на обекта rangeЗадайте OldRange = MyRange'Добавете адреса към низ, разделящ с "|" характерFindStr = FindStr & "|" & MyRange.Address„Повтаряйте обхвата, търсейки други случаиНаправете„Търсете„ Light & Heat “, като използвате предишния намерен адрес като параметър AfterЗадайте 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 за да посочите в кой компонент на клетката искате да търсите. Можете да посочите стойности, формули или коментари в клетка.
- xlValues - Търси стойности на клетката (крайната стойност на клетка след нейното изчисление)
- xl Формули - Търси в самата формула на клетката (каквото и да е въведено в клетката)
- xlКоментари - Търси в бележките на клетката
- xlCommentsThreaded - Търсения в коментарите на клетката
Ако приемем, че формула е въведена в работния лист, можете да използвате този примерен код, за да намерите първото местоположение на всяка формула:
| 12345678910 | Sub TestLookIn ()Dim MyRange As RangeЗадайте MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Ако не MyRange е нищоMsgBox MyRange.AddressИначеMsgBox "Не е намерен"Край АкоEnd Sub |
Ако параметърът „LookIn“ е зададен на xlValues, кодът ще покаже съобщение „Не е намерено“. В този пример той ще върне B10.
Използване на параметъра LookAt
The Параметър LookAt определя дали find ще търси точно съвпадение на клетка или ще търси всяка клетка, съдържаща стойността на търсене.
- xl Целият - Изисква цялата клетка да съответства на стойността за търсене
- xlPart - Търси в клетката низа за търсене
Този пример за код ще намери първата клетка, съдържаща текста „светлина“. С Търсене: = xlPart, той ще върне съвпадение за „Светлина и топлина“.
| 123456789 | Sub TestLookAt ()Dim MyRange As RangeЗадайте MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Ако не MyRange е нищоMsgBox MyRange.AddressИначеMsgBox "Не е намерен"Край Ако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ИначеMsgBox "Не е намерен"Край Ако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ИначеMsgBox "Не е намерен"Край Ако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 = Листове ("Sheet1"). UsedRange.Find ("топлина", Формат за търсене: = Вярно)Ако не MyRange е нищоMsgBox MyRange.AddressИначеMsgBox "Не е намерен"Край АкоApplication.FindFormat.ClearEnd Sub |
В този пример, FindFormat property е настроен да търси удебелен шрифт. След това изявлението Find търси думата „топлина“, задавайки параметъра SearchFormat на True, така че да върне екземпляр от този текст само ако шрифтът е удебелен.
В примерните данни на работния лист, показани по -рано, това ще върне A9, която е единствената клетка, съдържаща думата „топлина“ с удебелен шрифт.
Уверете се, че свойството FindFormat е изчистено в края на кода. Ако не направите това, следващото ви търсене ще вземе предвид това и ще върне неправилни резултати.
Когато използвате параметър SearchFormat, можете също да използвате заместващ знак (*) като стойност за търсене. В този случай той ще търси всяка стойност с удебелен шрифт:
| 1 | Задайте MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Формат за търсене: = 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 "Не е намерен"Край АкоEnd Sub |
В този пример кодът ще върне A4, но ако сме използвали само част от текста напр. „Топлина“, нищо няма да бъде намерено, защото съвпадаме по цялата стойност на клетката. Също така, той ще се провали поради несъответствие на случая.
| 1 | Задайте MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Заменете в Excel VBA
Както можете да очаквате, има функция Replace в Excel VBA, която работи по много подобен начин на „Find“, но замества стойностите в местоположението на клетката, намерени с нова стойност.
Това са параметрите, които можете да използвате в инструкция Replace method. Те работят по абсолютно същия начин, както при инструкцията Find method. Единствената разлика от „Намери“ е, че трябва да посочите параметър за замяна.
| Име | Тип | Описание | Стойности |
| Какво | Задължително | Стойността за търсене | Всеки тип данни, като низ или число |
| Замяна | Задължително | Заменящият низ. | Всеки тип данни, като низ или число |
| Погледнете | По избор | Свържете част или цяла клетка | xlPart или xlWhole |
| SearchOrder | По избор | Редът за търсене в - Редове или Колони | xlByRows или xlByColumns |
| MatchCase | По избор | Търсенето е чувствително към регистъра или не | Истина или лъжа |
| MatchByte | По избор | Използва се само ако сте инсталирали поддръжка на двубайтови езици | Истина или лъжа |
| Формат за търсене | По избор | Разрешаване на търсене по формат на клетката | Истина или лъжа |
| ReplaceFormat | По избор | Форматът за замяна на метода. | Истина или лъжа |
Параметърът Замяна на формат търси клетка с определен формат, напр. удебелен по същия начин, както параметърът SearchFormat работи в метода Find. Първо трябва да настроите свойството Application.FindFormat, както е показано в кода Find example, показан по -рано
Смяна без допълнителни параметри
Най -простото, трябва само да посочите какво търсите и с какво искате да го замените.
| 123 | Sub TestReplace ()Листове ("Sheet1"). UsedRange.Replace What: = "Light & Heat", Замяна: = "L & H"End Sub |
Обърнете внимание, че методът Find ще върне само първия екземпляр на съответстващата стойност, докато методът Replace работи през целия посочен диапазон и замества всичко, на което намери съвпадение.
Кодът за подмяна, показан тук, ще замени всеки екземпляр от „Light & Heat“ с „L & H“ през целия диапазон от клетки, дефиниран от обекта UsedRange
Използване на VBA за намиране или замяна на текст в текстов низ на VBA
Горните примери работят чудесно, когато използвате VBA за взаимодействие с данни на Excel. За взаимодействие с низове VBA обаче можете да използвате вградени VBA функции като INSTR и REPLACE.
Можете да използвате INSTR функция за намиране на низ от текст в по -дълъг низ.
| 123 | Sub TestInstr ()MsgBox InStr ("Това е MyText низ", "MyText")End Sub |
Този примерен код ще върне стойността на 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", "Моят текст")End Sub |
Този код заменя „MyText“ с „My Text“. Обърнете внимание, че низът за търсене е чувствителен към регистъра, тъй като двоичното сравнение е по подразбиране.
Можете също така да добавите други незадължителни параметри:
- Старт - определя позиция в първоначалния низ, от която трябва да започне подмяната. За разлика от метода Find, той връща отсечен низ, започващ от номера на знака, определен от параметъра Start.
- Броя - определя броя на замените, които трябва да бъдат направени. По подразбиране Replace ще промени всеки екземпляр от намерения текст за търсене, но можете да ограничите това до единична подмяна, като зададете параметъра Count на 1
- Сравнете - както в метода Find, можете да зададете двоично търсене или текстово търсене с помощта vbBinaryCompare или vbTextCompare. Двоичният регистър е чувствителен, а текстът не е чувствителен към регистъра
| 1 | MsgBox Replace ("Това е MyText низ (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Този код връща „Моят текстов низ (митекст)“. Това е така, защото дадената начална точка е 9, така че новият върнат низ започва от символ 9. Само първият „MyText“ е променен, тъй като параметърът Count е зададен на 1.
Методът Replace е идеален за решаване на проблеми като имена на хора, съдържащи апострофи, напр. О’Флин. Ако използвате единични кавички за дефиниране на стойност на низ и има апостроф, това ще доведе до грешка, тъй като кодът ще интерпретира апострофа като края на низ и няма да разпознае остатъка от низ.
Можете да използвате метода Replace, за да замените апострофа с нищо, като го премахнете напълно.
