See õpetus näitab, kuidas kasutada Excel VBA otsimis- ja asendamismeetodeid.
VBA leid
Excelil on suurepärane sisseehitatud funktsioon Leia ja Otsi ja asenda tööriistad.
Neid saab aktiveerida otseteede abil CTRL + F. (Leia) või CTRL + H (Asenda) või lindi kaudu: Avaleht> Redigeerimine> Otsi ja vali.
Klikkides Valikud, näete täpsemaid otsinguvõimalusi:
VBA abil saate hõlpsasti juurde pääseda nii otsimis- kui ka asendamismeetoditele. Need sisseehitatud meetodid on palju kiiremad kui miski muu, mida võiksite ise VBA-sse kirjutada.
Leidke VBA näide
Otsimisfunktsiooni demonstreerimiseks lõime lehel1 järgmise andmekogumi.
Kui soovite seda jälgida, sisestage andmed oma töövihikusse.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA leid ilma valikuliste parameetriteta
VBA Find meetodi kasutamisel saate määrata palju valikulisi parameetreid.
Soovitame tungivalt määratleda kõik parameetrid, kui kasutate otsingumeetodit!
Kui te ei määra valikulisi parameetreid, kasutab VBA Exceli otsinguaknas parajasti valitud parameetreid. See tähendab, et te ei pruugi koodi käivitamisel teada, milliseid otsinguparameetreid kasutatakse. Otsingut saab käivitada kogu töövihikus või lehel. See võib otsida valemeid või väärtusi. Seda pole võimalik teada saada, kui te ei kontrolli käsitsi Exceli otsinguaknas valitud asju.
Lihtsuse huvides alustame näitega, kus valikulisi parameetreid pole määratletud.
Näide lihtsast otsimisest
Vaatame lihtsat otsingu näidet:
| 123456789 | Sub TestFind ()Dim MyRange vahemikunaMäära MyRange = Sheets ("Sheet1"). UsedRange.Find ("töötaja")MsgBox MyRange. AadressMsgBox MyRange.VeergMsgBox MyRange.RidaEnd Sub |
See kood otsib lehe 1 kasutatud vahemikus sõna „töötaja”. Kui see leiab „töötaja”, määrab ta esimese leitud vahemiku vahemiku muutujale MyRange.
Järgmisena kuvatakse sõnumikastid koos leitud teksti aadressi, veeru ja reaga.
Selles näites kasutatakse otsingu vaikeseadeid (eeldusel, et neid pole Exceli otsinguaknas muudetud):
- Otsingutekst on osaliselt vastavuses lahtri väärtusega (täpne lahtrivaste pole nõutav)
- Otsing ei ole tõstutundlik.
- Otsi otsib ainult ühte töölehte
Neid seadeid saab muuta erinevate valikuliste parameetritega (arutatakse allpool).
Leidke meetodi märkused
- Otsing ei vali lahtrit, kust tekst leitakse. See tuvastab ainult leitud vahemiku, mida saate oma koodis manipuleerida.
- Otsimismeetod tuvastab ainult esimese leitud eksemplari.
- Võite kasutada metamärke (*) nt. otsi "E*"
Ei leitud midagi
Kui otsinguteksti pole olemas, jääb vahemiku objekt tühjaks. See põhjustab suurt probleemi, kui teie kood proovib kuvada asukohaväärtusi, kuna neid pole olemas. Selle tulemuseks on veateade, mida te ei soovi.
Õnneks saate tühja vahemiku objekti VBA -s testida, kasutades operaatorit Is:
| 1 | Kui mitte MyRange pole siis midagi |
Koodi lisamine meie eelmisele näitele:
| 12345678910111213 | Sub TestFind ()Dim MyRange vahemikunaMäära MyRange = Sheets ("Sheet1"). UsedRange.Find ("töötaja")Kui mitte MyRange pole siis midagiMsgBox MyRange. AadressMsgBox MyRange.VeergMsgBox MyRange.RidaMuiduMsgBox "Ei leitud"Lõpp KuiEnd Sub |
Leidke parameetrid
Siiani oleme vaadelnud ainult põhinäidet meetodi Find kasutamise kohta. Siiski on saadaval mitmeid valikulisi parameetreid, mis aitavad otsingut täpsustada
| Parameeter | Tüüp | Kirjeldus | Väärtused |
| Mida | Nõutud | Otsitav väärtus | Mis tahes andmetüüp, näiteks string või numbriline |
| Pärast | Valikuline | Otsingu alustamiseks ühe lahtri viide | Lahtri aadress |
| Vaata sisse | Valikuline | Kasutage otsimiseks valemeid, väärtusi, kommentaare | xlVäärtused, xlVormelid, xlKommentaarid |
| Vaata | Valikuline | Sobitage lahtri osa või kogu | xlTervik, xlosa |
| SearchOrder | Valikuline | Otsimise järjekord - ridades või veergudes | xlByRows, xlByColummns |
| SearchDirection | Valikuline | Otsingu suund - edasi või tagasi | xlJärgmine, xlEelmine |
| Tikutoos | Valikuline | Otsing on tõstutundlik või mitte | Õige või vale |
| MatchByte | Valikuline | Kasutatakse ainult siis, kui olete installinud kahebaidise keeletoe, nt. Hiina keel | Õige või vale |
| Otsinguvorming | Valikuline | Luba otsing lahtri vormingu järgi | Õige või vale |
Pärast parameetrit ja leidke mitu väärtust
Kasutate Pärast parameetrit otsingu alguslahtri määramiseks. See on kasulik, kui otsitavat väärtust on rohkem kui üks.
Kui otsing on juba leidnud ühe väärtuse ja teate, et väärtusi leitakse rohkem, siis kasutage esimese eksemplari salvestamiseks meetodit Otsi parameetriga „Pärast” ja seejärel kasutage seda lahtrit järgmise otsingu lähtepunktiks.
Seda saate kasutada otsinguteksti mitme eksemplari leidmiseks.
| 123456789101112131415161718192021222324252627282930313233343536 | AlamkatseMultipleFinds ()Dim MyRange vahemikuna, OldRange vahemikuna, FindStr stringina"Otsige esimest korda" "Valgus ja kuumus" "Määra MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")"Kui seda ei leita, väljugeKui MyRange pole midagi, siis väljuge alamist'Leiti esimene aadressMsgBox MyRange. Aadress'Tehke vahemiku objektist koopiaMäära OldRange = MyRange"Lisage aadress" | "eraldavale stringile iseloomFindStr = FindStr & "|" & MyRange. Aadress„Korda ringi, otsides teisi juhtumeidTehkeOtsige 'Light & Heat', kasutades parameetrina After leitud eelmise leitud aadressiMäära MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Kui aadress on juba leitud, väljuge do loopist - see peatab pideva loopimiseKui InStr (FindStr, MyRange.Address), siis Välju Do'Kuva uusim leitud aadressMsgBox MyRange. Aadress„Lisage aadresside jadale uusim aadressFindStr = FindStr & "|" & MyRange. Aadress"tehke praegusest vahemikust koopiaMäära OldRange = MyRangeLoopEnd Sub |
See kood kordab kasutatud vahemikku ja kuvab aadressi iga kord, kui leiab „Light & Heat” eksemplari
Pange tähele, et kood jätkab tsüklit, kuni FindStrist leitakse duplikaat, sel juhul väljub see Do -ahelast.
Vaata parameetrit
Võite kasutada LookIn parameeter lahtri komponendi otsimiseks. Lahtris saate määrata väärtusi, valemeid või kommentaare.
- xlValues - Otsib lahtrite väärtusi (lahtri lõplik väärtus pärast selle arvutamist)
- xlVormelid - Otsib lahtri valemis (ükskõik, mis lahtrisse sisestatakse)
- xlKommentaarid - Otsib lahtrite märkmetes
- xlKommentaarid - Otsib lahtrite kommentaarides
Eeldades, et töölehele on sisestatud valem, võite selle näite koodi abil leida mis tahes valemi esimese asukoha:
| 12345678910 | Sub TestLookIn ()Dim MyRange vahemikunaMäära MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Kui mitte MyRange pole siis midagiMsgBox MyRange. AadressMuiduMsgBox "Ei leitud"Lõpp KuiEnd Sub |
Kui parameetri „LookIn” väärtuseks oli määratud xlValues, kuvatakse koodis sõnum „Not Found”. Selles näites tagastab see B10.
LookAt parameetri kasutamine
The LookAt parameeter määrab, kas leid otsib täpset lahtrivastet või otsib mis tahes lahtrit, mis sisaldab otsinguväärtust.
- xlTervik - nõuab, et kogu lahter vastaks otsinguväärtusele
- xlosa - Otsib lahtrist otsingustringi
See koodinäide leiab esimese lahtri, mis sisaldab teksti “kerge”. Koos Lookat: = xlPart, see tagastab mängu “Light & Heat”.
| 123456789 | AlamkatseLookAt ()Dim MyRange vahemikunaMäära MyRange = Sheets ("Sheet1"). UsedRange.Find ("kerge", Lookat: = xlPart)Kui mitte MyRange pole siis midagiMsgBox MyRange. AadressMuiduMsgBox "Ei leitud"Lõpp KuiEnd Sub |
Kui xlTervik oli määratud, saab vaste tagasi ainult siis, kui lahtri väärtus on “hele”.
SearchOrder parameeter
The SearchOrder parameeter määrab, kuidas otsingut läbi viiakse kogu vahemikus.
- xlRidad - Otsing toimub rida rida
- xlVeerud - Otsing toimub veerghaaval
| 123456789 | Sub TestSearchOrder ()Dim MyRange vahemikunaMäära MyRange = Sheets ("Sheet1"). UsedRange.Find ("töötaja", SearchOrder: = xlColumns)Kui mitte MyRange pole siis midagiMsgBox MyRange. AadressMuiduMsgBox "Ei leitud"Lõpp KuiEnd Sub |
See mõjutab seda, milline vaste leitakse esimesena.
Kasutades varem töölehele sisestatud testiandmeid, kui otsingukord on veerud, asub lahter A5. Kui otsingukorralduse parameeter on muudetud väärtuseks xlRows, on leitud lahter C4
See on oluline, kui teil on otsinguvahemikus dubleerivaid väärtusi ja soovite leida esimese eksemplari kindla veeru nime all.
SearchDirection parameeter
The SearchDirection parameeter dikteerib, millises suunas otsing läheb - tõhusalt edasi või tagasi.
- xlJärgmine - Otsige vahemikust järgmist sobivat väärtust
- xlEelmine - Otsi vahemikku eelnevat sobivat väärtust
Jällegi, kui otsinguvahemikus on dubleerivaid väärtusi, võib see mõjutada seda, milline neist esimesena leitakse.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange vahemikunaMäära MyRange = Sheets ("Sheet1"). UsedRange.Find ("soojus", SearchDirection: = xlPrevious)Kui mitte MyRange pole siis midagiMsgBox MyRange. AadressMuiduMsgBox "Ei leitud"Lõpp KuiEnd Sub |
Kasutades seda koodi katseandmetel, tagastab otsingu suund xlPrevious asukoha C9. Parameetri xlNext kasutamine tagastab asukoha A4.
Järgmine parameeter tähendab, et otsing algab otsinguvahemiku vasakus ülanurgas ja töötab allapoole. Eelmine parameeter tähendab, et otsing algab otsinguvahemiku paremas alanurgas ja liigub ülespoole.
MatchByte parameeter
The MatchBye parameeter kasutatakse ainult keelte puhul, mis kasutavad iga tähemärgi tähistamiseks topeltbaiti (nt hiina, vene ja jaapani).
Kui see parameeter on seatud väärtusele „Tõene”, sobitab otsing ainult kahebaidiste tähemärkidega kahebaidiste tähemärkidega. Kui parameetri väärtuseks on määratud „Väär”, sobib kahebaidine märk ühe- või kahebaidiste tähemärkidega.
Otsinguvormi parameeter
The SearchFormat parameeter võimaldab otsida sobivaid lahtrivorminguid. See võib olla konkreetne kasutatav font, paks font või teksti värv. Enne selle parameetri kasutamist peate määrama atribuudi Application.FindFormat abil otsimiseks vajaliku vormingu.
Siin on näide selle kasutamise kohta:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange vahemikunaApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TõsiMäära MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Kui mitte MyRange pole siis midagiMsgBox MyRange. AadressMuiduMsgBox "Ei leitud"Lõpp KuiApplication.FindFormat.ClearEnd Sub |
Selles näites on FindFormat atribuut on seatud otsima rasvast fonti. Lause Find otsib seejärel sõna „heat”, seades parameetri SearchFormat väärtuseks True, nii et see tagastab selle teksti eksemplari ainult siis, kui font on paks.
Varem näidatud töölehtede näidisandmetes tagastatakse see A9, mis on ainus lahter, mis sisaldab rasvases kirjas sõna „soojus”.
Veenduge, et FindFormat atribuut on koodi lõpus tühjendatud. Kui te seda ei tee, võtab teie järgmine otsing seda siiski arvesse ja tagastab valed tulemused.
Kui kasutate parameetrit SearchFormat, saate otsinguväärtusena kasutada ka metamärki (*). Sel juhul otsib see mis tahes väärtust paksus kirjas:
| 1 | Määra MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Mitme parameetri kasutamine
Kõiki siin käsitletud otsinguparameetreid saab vajadusel kasutada koos.
Näiteks võite kombineerida parameetri „LookIn” parameetriga „MatchCase”, nii et vaatate kogu lahtriteksti, kuid see on tõstutundlik
| 123456789 | AlamkatseMultipleParameters ()Dim MyRange vahemikunaMäära MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Kui mitte MyRange pole siis midagiMsgBox MyRange. AadressMuiduMsgBox "Ei leitud"Lõpp KuiEnd Sub |
Selles näites tagastab kood A4, kuid kui kasutasime ainult osa tekstist nt. "Soojus", midagi ei leita, sest me vastame kogu lahtri väärtusele. Samuti ebaõnnestus see juhtumi mittevastavuse tõttu.
| 1 | Määra MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Asenda Excel VBA -s
Nagu arvata võib, on Exceli VBA -s asendusfunktsioon, mis töötab sarnaselt otsimisega, kuid asendab leitud lahtri asukohas olevad väärtused uue väärtusega.
Need on parameetrid, mida saate kasutada asendusmeetodi avalduses. Need toimivad täpselt samamoodi nagu Find meetodi avalduse puhul. Ainus erinevus otsingu puhul on see, et peate määrama asendusparameetri.
| Nimi | Tüüp | Kirjeldus | Väärtused |
| Mida | Nõutud | Otsitav väärtus | Mis tahes andmetüüp, näiteks string või numbriline |
| Asendamine | Nõutud | Asendusnöör. | Mis tahes andmetüüp, näiteks string või numbriline |
| Vaata | Valikuline | Sobitage lahtri osa või kogu | xlPart või xlWhole |
| SearchOrder | Valikuline | Otsimisjärjestus - read või veerud | xlByRows või xlByColumns |
| Tikutoos | Valikuline | Otsing on tõstutundlik või mitte | Õige või vale |
| MatchByte | Valikuline | Kasutatakse ainult siis, kui olete installinud kahebaidise keeletoe | Õige või vale |
| Otsinguvorming | Valikuline | Luba otsing lahtri vormingu järgi | Õige või vale |
| Asenda vorming | Valikuline | Meetodi asendusvorming. | Õige või vale |
Parameeter Asenda vorming otsib lahtrit kindla vorminguga, nt. rasvane samal viisil, nagu parameeter SearchFormat töötab otsimismeetodil. Esmalt peate määrama atribuudi Application.FindFormat, nagu on näidatud varem näidatud näidiskoodi leidmisel
Asenda ilma valikuliste parameetriteta
Lihtsamalt öeldes peate täpsustama, mida otsite ja millega soovite selle asendada.
| 123 | Sub TestReplace ()Lehed ("Sheet1"). UsedRange.Replace What: = "Light & Heat", Replacement: = "L & H"End Sub |
Pange tähele, et otsingumeetod tagastab ainult sobitatud väärtuse esimese eksemplari, samas kui asendusmeetod töötab kogu määratud vahemikus ja asendab kõik, millele see vaste leiab.
Siin näidatud asenduskood asendab iga „Light & Heat“ eksemplari tähega „L & H“ kogu kasutatud objekti määratletud lahtrite vahemikus.
VBA kasutamine teksti leidmiseks või asendamiseks VBA tekstistringis
Ülaltoodud näited toimivad suurepäraselt, kui kasutate VBA -d Exceli andmetega suhtlemiseks. VBA stringidega suhtlemiseks saate aga kasutada sisseehitatud VBA funktsioone, nagu INSTR ja REPLACE.
Võite kasutada Funktsioon INSTR teksti stringi leidmiseks pikema stringi piires.
| 123 | Sub TestInstr ()MsgBox InStr ("See on MyText string", "MyText")End Sub |
See näite kood tagastab väärtuse 9, mis on numbrikoht, kus otsitavast stringist leitakse „MyText”.
Pange tähele, et see on tõstutundlik. Kui „MyText” on kõik väiketähed, tagastatakse väärtus 0, mis tähendab, et otsingustringi ei leitud. Allpool käsitleme, kuidas keelata tõstutundlikkus.
INSTR - Alusta
Saadaval on veel kaks valikulist parameetrit. Otsingu alguspunkti saate määrata:
| 1 | MsgBox InStr (9, "See on MyText string", "MyText") |
Alguspunkt on määratud 9 -ks, nii et see naaseb ikkagi 9. Kui alguspunkt oli 10, tagastab see 0 (vastet pole), kuna alguspunkt oleks liiga kaugele ettepoole.
INSTR - tõstutundlikkus
Samuti saate määrata parameetri Võrdle väärtuseks vbBinaryCompare või vbTextCompare. Kui määrate selle parameetri, peab avaldusel olema algparameetri väärtus.
- vbBinaryCompare - tõstutundlik (vaikimisi)
- vbTextCompare - Ei ole tõstutundlik
| 1 | MsgBox InStr (1, "See on MyText string", "mytext", vbTextCompare) |
See avaldus tagastab ikkagi 9, kuigi otsingutekst on väiketähtedega.
Väiketähtede tundlikkuse keelamiseks võite koodimooduli ülaosas deklareerida ka suvandi Võrdle teksti.
VBA asendusfunktsioon
Kui soovite asendada stringis olevad märgid oma koodis erineva tekstiga, sobib asendusmeetod selleks ideaalselt:
| 123 | Sub TestReplace ()MsgBox Replace ("See on MyText string", "MyText", "My Text")End Sub |
See kood asendab „MyText” tekstiga „My Text”. Pange tähele, et otsingustring on tõstutundlik, kuna binaarne võrdlus on vaikimisi.
Saate lisada ka muid valikulisi parameetreid:
- Alusta - määratleb positsiooni algses stringis, millest asendamine peab algama. Erinevalt otsingumeetodist tagastab see kärbitud stringi, mis algab parameetriga Start määratud märginumbrist.
- Krahv - määratleb tehtavate asenduste arvu. Vaikimisi muudab asendamine otsitava teksti kõiki eksemplare, kuid saate piirata seda ühe asendusega, määrates parameetri Count väärtuseks 1
- Võrdlema - nagu ka otsingumeetodi puhul, saate määrata binaarotsingu või tekstiotsingu vbBinaryCompare või vbTextCompare. Binaar on tõstutundlik ja tekst ei erista neid
| 1 | MsgBox Replace ("See on MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
See kood tagastab 'Minu teksti string (mytext)'. Seda seetõttu, et antud alguspunkt on 9, seega algab uus tagastatud string 9 -st tähemärgist. Ainult esimest 'MyText' on muudetud, kuna parameeter Count on seatud väärtusele 1.
Asendusmeetod sobib ideaalselt selliste probleemide lahendamiseks nagu inimeste nimed, mis sisaldavad apostroofe, nt. O’Flynn. Kui kasutate stringi väärtuse määratlemiseks üksikuid jutumärke ja seal on apostroof, põhjustab see tõrke, kuna kood tõlgendab apostroofi stringi lõpuna ega tunnista ülejäänud stringi.
Võite kasutada asendusmeetodit, et asendada apostroof mitte millegagi, eemaldades selle täielikult.
