Šī apmācība parādīs, kā izmantot Excel VBA meklēšanas un aizstāšanas metodes.
VBA Atrast
Excel ir lieliski iebūvēts Atrast un Atrast un aizstāt instrumentus.
Tos var aktivizēt, izmantojot īsceļus CTRL + F. (Atrast) vai CTRL + H (Aizstāt) vai caur lenti: Sākums> Rediģēšana> Atrast un atlasīt.
Noklikšķinot Iespējas, jūs varat redzēt izvērstās meklēšanas iespējas:
Izmantojot VBA, varat viegli piekļūt gan atrašanas, gan aizstāšanas metodēm. Šīs iebūvētās metodes ir daudz ātrāk nekā jebkas, ko jūs varētu rakstīt pats VBA.
Atrodiet VBA piemēru
Lai parādītu funkcionalitāti Atrast, mēs izveidojām šādu datu kopu lapā 1. lapa.
Ja vēlaties sekot līdzi, ievadiet datus savā darbgrāmatā.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA Find bez papildu parametriem
Izmantojot VBA Find metodi, varat iestatīt daudz papildu parametru.
Mēs ļoti iesakām definēt visus parametrus, kad izmantojat meklēšanas metodi!
Ja jūs nenosakāt papildu parametrus, VBA izmantos pašlaik atlasītos parametrus Excel meklēšanas logā. Tas nozīmē, ka jūs, iespējams, nezināt, kādi meklēšanas parametri tiek izmantoti, kad tiek izpildīts kods. Atrašanu var veikt visā darbgrāmatā vai lapā. Tas varētu meklēt formulas vai vērtības. To nav iespējams uzzināt, ja vien manuāli nepārbaudāt, kas pašlaik ir atlasīts Excel meklēšanas logā.
Vienkāršības labad mēs sāksim ar piemēru, kurā nav noteikti papildu parametri.
Vienkāršas meklēšanas piemērs
Apskatīsim vienkāršu atrašanas piemēru:
| 123456789 | Sub TestFind ()Dim MyRange kā diapazonsIestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("darbinieks")MsgBox MyRange. AdreseMsgBox MyRange.ColumnMsgBox MyRange.RowBeigu apakš |
Šis kods 1. lapas izmantotajā diapazonā meklē “darbinieks”. Ja tas atrod “darbinieku”, tas pirmo atrasto diapazonu piešķirs diapazona mainīgajam MyRange.
Tālāk tiks parādīti ziņojumu lodziņi ar atrastā teksta adresi, kolonnu un rindu.
Šajā piemērā tiek izmantoti noklusējuma meklēšanas iestatījumi (pieņemot, ka tie nav mainīti Excel meklēšanas logā):
- Meklēšanas teksts daļēji atbilst šūnas vērtībai (precīza šūnu atbilstība nav nepieciešama)
- Meklēšana nav reģistrjutīga.
- Atrast meklē tikai vienā darblapā
Šos iestatījumus var mainīt, izmantojot dažādus papildu parametrus (aplūkoti turpmāk).
Atrodiet metodes piezīmes
- Atrodot, netiek atlasīta šūna, kurā atrodams teksts. Tas identificē tikai atrasto diapazonu, ar kuru jūs varat manipulēt savā kodā.
- Atrast metode atradīs tikai pirmo atrasto instanci.
- Varat izmantot aizstājējzīmes (*), piem. meklēt “E*”
Nekas nav atrasts
Ja meklēšanas teksts neeksistē, diapazona objekts paliks tukšs. Tas rada lielu problēmu, kad jūsu kods mēģina parādīt atrašanās vietas vērtības, jo tās nepastāv. Rezultātā parādīsies kļūdas ziņojums, kuru nevēlaties.
Par laimi, varat pārbaudīt tukšu diapazona objektu VBA, izmantojot Is Operator:
| 1 | Ja ne MyRange nav nekas, tad |
Koda pievienošana mūsu iepriekšējam piemēram:
| 12345678910111213 | Sub TestFind ()Dim MyRange kā diapazonsIestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("darbinieks")Ja ne MyRange nav nekas, tadMsgBox MyRange. AdreseMsgBox MyRange.ColumnMsgBox MyRange.RowCitādiMsgBox "Nav atrasts"Beigas JaBeigu apakš |
Atrodiet parametrus
Līdz šim mēs esam apskatījuši tikai pamata piemēru Find metodes izmantošanai. Tomēr ir pieejami vairāki papildu parametri, kas palīdzēs uzlabot meklēšanu
| Parametrs | Tips | Apraksts | Vērtības |
| Kas | Obligāti | Vērtība, ko meklēt | Jebkurš datu veids, piemēram, virkne vai cipars |
| Pēc | Neobligāti | Atsauce uz vienu šūnu, lai sāktu meklēšanu | Šūnas adrese |
| Ieskaties | Neobligāti | Meklēšanai izmantojiet formulas, vērtības, komentārus | xlVērtības, xlFormulas, xlKomentāri |
| Paskaties uz | Neobligāti | Saskaņojiet šūnas daļu vai visu | xlVesela, xlDaļa |
| SearchOrder | Neobligāti | Meklēšanas secība rindās vai kolonnās | xlByRows, xlByColummns |
| SearchDirection | Neobligāti | Meklēšanas virziens - uz priekšu vai atpakaļ | xlNākamais, xl |
| MatchCase | Neobligāti | Meklēšana ir reģistrjutīga vai ne | Patiesība vai meli |
| MatchByte | Neobligāti | Izmanto tikai tad, ja esat instalējis dubultbaitu valodas atbalstu, piem. Ķīniešu valoda | Patiesība vai meli |
| SearchFormat | Neobligāti | Atļaut meklēšanu pēc šūnas formāta | Patiesība vai meli |
Pēc parametra un atrodiet vairākas vērtības
Jūs izmantojat Pēc parametra lai norādītu jūsu meklēšanas sākuma šūnu. Tas ir noderīgi, ja meklējamās vērtības ir vairāk nekā viens gadījums.
Ja meklēšana jau ir atradusi vienu vērtību un jūs zināt, ka tiks atrasts vairāk vērtību, tad, lai ierakstītu pirmo gadījumu, izmantojiet metodi Atrast ar parametru “Pēc” un pēc tam izmantojiet šo šūnu kā sākumpunktu nākamajai meklēšanai.
Varat to izmantot, lai atrastu vairākus meklēšanas teksta gadījumus:
| 123456789101112131415161718192021222324252627282930313233343536 | ApakštestsMultipleFinds ()Dim MyRange kā diapazons, OldRange kā diapazons, FindStr kā virkne"Meklējiet" Light & Heat "pirmo gadījumuIestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")'Ja netiek atrasts, izejietJa MyRange nav nekas, izejiet no apakšnodaļas'Atrasta pirmā displeja adreseMsgBox MyRange. Adrese'Izveidojiet diapazona objekta kopijuIestatīt OldRange = MyRange'Pievienojiet adresi virknei, kas norobežo ar "|" rakstursFindStr = FindStr & "|" & MyRange. Adrese“Atkārtojiet diapazonu, meklējot citus gadījumusVaiMeklējiet “Light & Heat”, izmantojot parametru After pēc iepriekš atrastās adresesIestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Ja adrese jau ir atrasta, izejiet no do cilpas - tas pārtrauc nepārtraukto cilpuJa InStr (FindStr, MyRange.Address), tad izejiet Do'Parādīt jaunāko atrasto adresiMsgBox MyRange. Adrese“Pievienojiet adrešu virknei jaunāko adresiFindStr = FindStr & "|" & MyRange. Adrese'izveidojiet pašreizējā diapazona kopijuIestatīt OldRange = MyRangeCilpaBeigu apakš |
Šis kods atkārtos izmantoto diapazonu un parādīs adresi katru reizi, kad atrod “Light & Heat” gadījumu
Ņemiet vērā, ka kods turpinās darboties, līdz FindStr tiks atrasta dublikāta adrese, un tādā gadījumā tas izies no cilpas Do.
Skatīties parametrā
Jūs varat izmantot LookIn parametrs lai norādītu, kurā šūnas komponentā vēlaties meklēt. Šūnā varat norādīt vērtības, formulas vai komentārus.
- xlValues - Meklē šūnu vērtības (šūnas galīgā vērtība pēc aprēķināšanas)
- xlFormulas - Meklēšana pašā šūnas formulā (neatkarīgi no tā, kas ir ievadīts šūnā)
- xlKomentāri - Meklējumi šūnu piezīmēs
- xlCommentsThreaded - Meklējumi šūnu komentāros
Pieņemot, ka darblapā ir ievadīta formula, varat izmantot šo koda piemēru, lai atrastu jebkuras formulas pirmo atrašanās vietu:
| 12345678910 | Sub TestLookIn ()Dim MyRange kā diapazonsIestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Ja ne MyRange nav nekas, tadMsgBox MyRange. AdreseCitādiMsgBox "Nav atrasts"Beigas JaBeigu apakš |
Ja parametrs “LookIn” ir iestatīts uz xlValues, kodā tiek parādīts ziņojums “Not Found”. Šajā piemērā tas atgriezīs B10.
Izmantojot LookAt parametru
The LookAt parametrs nosaka, vai atradums meklēs precīzu šūnu atbilstību, vai meklēs jebkuru šūnu, kurā ir meklēšanas vērtība.
- xlViss - Nepieciešama visa šūna, kas atbilst meklēšanas vērtībai
- xlDaļa - Šūnā meklē meklēšanas virkni
Šis koda piemērs atradīs pirmo šūnu, kurā ir teksts “gaisma”. Ar Skatīt: = xlPart, tas atgriezīs spēli “Light & Heat”.
| 123456789 | Sub TestLookAt ()Dim MyRange kā diapazonsIestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Ja ne MyRange nav nekas, tadMsgBox MyRange. AdreseCitādiMsgBox "Nav atrasts"Beigas JaBeigu apakš |
Ja xlViss tika iestatīts, atbilstība atgriezīsies tikai tad, ja šūnas vērtība būs “gaiša”.
SearchOrder parametrs
The SearchOrder parametrs nosaka, kā tiks veikta meklēšana visā diapazonā.
- xlRows - Meklēšana tiek veikta pa rindām
- xlXolonnas - Meklēšana tiek veikta pa kolonnām
| 123456789 | Sub TestSearchOrder ()Dim MyRange kā diapazonsIestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("darbinieks", SearchOrder: = xlColumns)Ja ne MyRange nav nekas, tadMsgBox MyRange. AdreseCitādiMsgBox "Nav atrasts"Beigas JaBeigu apakš |
Tas ietekmē to, kura atbilstība tiks atrasta vispirms.
Izmantojot iepriekš darblapā ievadītos testa datus, kad meklēšanas secība ir kolonnas, atrodamā šūna ir A5. Kad meklēšanas pasūtījuma parametrs tiek mainīts uz xlRows, atrodamā šūna ir C4
Tas ir svarīgi, ja meklēšanas diapazonā ir dublikātu vērtības un vēlaties atrast pirmo instanci ar konkrētu kolonnas nosaukumu.
SearchDirection parametrs
The SearchDirection parametrs nosaka, kurā virzienā tiks veikta meklēšana - efektīvi uz priekšu vai atpakaļ.
- xlNākamais - Meklējiet nākamo atbilstošo vērtību diapazonā
- xl - Meklēt iepriekšējo atbilstošo vērtību diapazonā
Atkal, ja meklēšanas diapazonā ir dublikātu vērtības, tas var ietekmēt to, kurš tiek atrasts pirmais.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange kā diapazonsIestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Ja ne MyRange nav nekas, tadMsgBox MyRange. AdreseCitādiMsgBox "Nav atrasts"Beigas JaBeigu apakš |
Izmantojot šo kodu testa datos, meklēšanas virziens xlPrevious atgriezīs C9 atrašanās vietu. Izmantojot parametru xlNext, tiks atgriezta A4 atrašanās vieta.
Nākamais parametrs nozīmē, ka meklēšana sāksies meklēšanas diapazona augšējā kreisajā stūrī un darbosies uz leju. Iepriekšējais parametrs nozīmē, ka meklēšana sāksies meklēšanas diapazona apakšējā labajā stūrī un darbosies uz augšu.
MatchByte parametrs
The MatchBye parametrs tiek izmantots tikai valodās, kurās tiek izmantots dubults baits, lai attēlotu katru rakstzīmi, piemēram, ķīniešu, krievu un japāņu.
Ja šis parametrs ir iestatīts uz “True”, tad funkcija Find atradīs tikai dubultbaitu rakstzīmes ar dubultbaitu rakstzīmēm. Ja parametrs ir iestatīts uz “False”, dubultbaita rakstzīme sakritīs ar viena vai divu baitu rakstzīmēm.
SearchFormat parametrs
The SearchFormat parametrs ļauj meklēt atbilstošus šūnu formātus. Tas var būt konkrēts izmantotais fonts, treknraksts vai teksta krāsa. Pirms šī parametra izmantošanas ir jāiestata meklēšanai nepieciešamais formāts, izmantojot rekvizītu Application.FindFormat.
Šeit ir piemērs, kā to izmantot:
| 12345678910111213 | ApakšpārbaudeSearchFormat ()Dim MyRange kā diapazonsApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = PatiessIestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Ja ne MyRange nav nekas, tadMsgBox MyRange. AdreseCitādiMsgBox "Nav atrasts"Beigas JaApplication.FindFormat.ClearBeigu apakš |
Šajā piemērā FindFormat īpašums ir iestatīts tā, lai meklētu treknrakstu. Pēc tam paziņojumā Find tiek meklēts vārds “heat”, iestatot SearchFormat parametru uz True, lai tas atgrieztu šī teksta gadījumu tikai tad, ja fonts ir treknrakstā.
Iepriekš parādītajos darblapas datu paraugos tiks parādīts A9, kas ir vienīgā šūna, kurā treknrakstā ir vārds “siltums”.
Pārliecinieties, vai rekvizīts FindFormat ir notīrīts koda beigās. Ja to nedarīsit, nākamajā meklēšanā tas joprojām tiks ņemts vērā un tiks parādīti nepareizi rezultāti.
Ja izmantojat SearchFormat parametru, kā meklēšanas vērtību varat izmantot arī aizstājējzīmi (*). Šajā gadījumā tā meklēs jebkuru vērtību ar treknrakstu:
| 1 | Iestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Izmantojot vairākus parametrus
Vajadzības gadījumā visus šeit aprakstītos meklēšanas parametrus var izmantot kopā.
Piemēram, varat apvienot parametru “LookIn” ar parametru “MatchCase”, lai apskatītu visu šūnas tekstu, taču tas ir reģistrjutīgs
| 123456789 | Apakšpārbaude Vairāki parametri ()Dim MyRange kā diapazonsIestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Ja ne MyRange nav nekas, tadMsgBox MyRange. AdreseCitādiMsgBox "Nav atrasts"Beigas JaBeigu apakš |
Šajā piemērā kods atgriezīs A4, bet, ja mēs izmantotu tikai daļu no teksta, piem. “Siltums”, nekas netiktu atrasts, jo mēs sakrītam ar visas šūnas vērtību. Turklāt tas neizdotos, jo lieta neatbilst.
| 1 | Iestatīt MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Aizstāt programmā Excel VBA
Kā jūs varētu gaidīt, programmā Excel VBA ir funkcija Aizstāt, kas darbojas ļoti līdzīgi kā “Atrast”, bet aizstāj atrastās šūnas vērtības ar jaunu vērtību.
Šie ir parametri, kurus varat izmantot aizstāšanas metodes paziņojumā. Tie darbojas tieši tāpat kā Find metodes paziņojums. Vienīgā atšķirība no “Atrast” ir tā, ka jums jānorāda aizstāšanas parametrs.
| Vārds | Tips | Apraksts | Vērtības |
| Kas | Obligāti | Vērtība, ko meklēt | Jebkurš datu veids, piemēram, virkne vai cipars |
| Aizvietošana | Obligāti | Aizvietošanas virkne. | Jebkurš datu veids, piemēram, virkne vai cipars |
| Paskaties uz | Neobligāti | Saskaņojiet šūnas daļu vai visu | xlDaļiņa vai xlVesela |
| SearchOrder | Neobligāti | Meklēšanas secība - rindas vai kolonnas | xlByRows vai xlByColumns |
| MatchCase | Neobligāti | Meklēšana ir reģistrjutīga vai ne | Patiesība vai meli |
| MatchByte | Neobligāti | Izmanto tikai tad, ja ir instalēts dubultbaitu valodas atbalsts | Patiesība vai meli |
| SearchFormat | Neobligāti | Atļaut meklēšanu pēc šūnas formāta | Patiesība vai meli |
| ReplaceFormat | Neobligāti | Metodes aizstāšanas formāts. | Patiesība vai meli |
Parametrs Aizstāt formātu meklē šūnu ar noteiktu formātu, piem. treknrakstā tādā pašā veidā, kā parametrs SearchFormat darbojas meklēšanas metodē. Vispirms ir jāiestata rekvizīts Application.FindFormat, kā parādīts iepriekš parādītajā parauga koda atrašanas attēlā
Aizstāt bez papildu parametriem
Vienkāršākajā gadījumā jums ir jānorāda tikai tas, ko meklējat un ar ko vēlaties to aizstāt.
| 123 | Apakšpārbaude ()Izklājlapas ("Sheet1"). UsedRange.Replace What: = "Light & Heat", Replacement: = "L & H"Beigu apakš |
Ņemiet vērā, ka metode Atrast atgriež tikai atbilstošās vērtības pirmo gadījumu, savukārt aizstāšanas metode darbojas visā norādītajā diapazonā un aizstāj visu, kam tā atrod atbilstību.
Šeit parādītais aizstāšanas kods aizstās katru “Light & Heat” gadījumu ar “L & H” visā šūnu diapazonā, ko nosaka objekts UsedRange.
VBA izmantošana, lai atrastu vai aizstātu tekstu VBA teksta virknē
Iepriekš minētie piemēri lieliski darbojas, ja izmantojat VBA, lai mijiedarbotos ar Excel datiem. Tomēr, lai mijiedarbotos ar VBA virknēm, varat izmantot iebūvētās VBA funkcijas, piemēram, INSTR un REPLACE.
Jūs varat izmantot Funkcija INSTR lai atrastu teksta virkni garākā virknē.
| 123 | Sub TestInstr ()MsgBox InStr ("Šī ir MyText virkne", "MyText")Beigu apakš |
Šis piemēra kods atgriež vērtību 9, kas ir numura pozīcija, kurā meklējamajā virknē ir atrodams “MyText”.
Ņemiet vērā, ka tas ir reģistrjutīgs. Ja “MyText” ir mazie burti, tiks atgriezta vērtība 0, kas nozīmē, ka meklēšanas virkne netika atrasta. Tālāk mēs apspriedīsim, kā atspējot reģistrjutību.
INSTR - Sākt
Ir pieejami vēl divi papildu parametri. Jūs varat norādīt meklēšanas sākumpunktu:
| 1 | MsgBox InStr (9, "Šī ir mana teksta virkne", "Mans teksts") |
Sākuma punkts ir norādīts kā 9, tāpēc tas joprojām atgriezīsies 9. Ja sākuma punkts būtu 10, tad tas atgrieztos 0 (neatbilst), jo sākuma punkts būtu pārāk tālu uz priekšu.
INSTR - reģistrjutība
Varat arī iestatīt parametru Salīdzināt uz vbBinaryCompare vai vbTextCompare. Ja iestatāt šo parametru, paziņojumam ir jābūt sākuma parametra vērtībai.
- vbBinaryCompare - reģistrjutīgs (noklusējums)
- vbTextCompare - Nav reģistrjutīgs
| 1 | MsgBox InStr (1, "Šī ir MyText virkne", "mytext", vbTextCompare) |
Šis paziņojums joprojām atgriezīs 9, pat ja meklēšanas teksts ir ar mazajiem burtiem.
Lai atspējotu reģistrjutību, koda moduļa augšpusē varat arī deklarēt opciju Salīdzināt tekstu.
VBA aizvietošanas funkcija
Ja vēlaties savā kodā aizstāt rakstzīmes virknē ar citu tekstu, aizstāšanas metode ir ideāli piemērota šim nolūkam:
| 123 | Apakšpārbaude ()MsgBox Replace ("Šī ir mana teksta virkne", "Mans teksts", "Mans teksts")Beigu apakš |
Šis kods “MyText” aizstāj ar “My Text”. Ņemiet vērā, ka meklēšanas virkne ir reģistrjutīga, jo binārais salīdzinājums ir noklusējums.
Varat arī pievienot citus izvēles parametrus:
- Sākt - nosaka pozīciju sākotnējā virknē, no kuras jāsāk nomaiņa. Atšķirībā no metodes Atrast, tā atgriež saīsinātu virkni, sākot ar rakstzīmes numuru, kas definēts ar parametru Sākt.
- Skaits - nosaka veicamo nomaiņu skaitu. Pēc noklusējuma Aizstāt mainīs katru atrastā meklēšanas teksta gadījumu, taču varat to ierobežot līdz vienam aizstājumam, iestatot parametru Skaits uz 1
- Salīdzināt - tāpat kā meklēšanas metodē, varat norādīt bināro meklēšanu vai teksta meklēšanu, izmantojot vbBinaryCompare vai vbTextCompare. Binārā ir reģistrjutīgs, bet teksts - ne
| 1 | MsgBox Replace ("Šī ir MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Šis kods atgriež “Mana teksta virkne (mans teksts)”. Tas ir tāpēc, ka norādītais sākuma punkts ir 9, tāpēc jaunā atgrieztā virkne sākas ar 9. rakstzīmi. Ir mainīts tikai pirmais MyText, jo parametrs Count ir iestatīts uz 1.
Aizstāšanas metode ir ideāli piemērota tādu problēmu risināšanai kā cilvēku vārdi, kas satur apostrofus, piem. O’Flinns. Ja virknes vērtības noteikšanai izmantojat pēdiņas un ir apostrofs, tas radīs kļūdu, jo kods interpretēs apostrofu kā virknes beigas un neatpazīs pārējo virkni.
Varat izmantot aizstāšanas metodi, lai aizstātu apostrofu ar neko, pilnībā noņemot to.
