Tämä opetusohjelma osoittaa, miten Excel VBA: n Etsi ja korvaa -menetelmiä käytetään.
VBA Etsi
Excelissä on erinomainen sisäänrakennettu löytö ja Etsi ja korvaa työkaluja.
Ne voidaan aktivoida pikanäppäimillä CTRL + F (Etsi) tai CTRL + H (Korvaa) tai valintanauhan kautta: Etusivu> Muokkaus> Etsi ja valitse.
Klikkaamalla Asetukset, näet tarkennetut hakuvaihtoehdot:
Voit käyttää sekä Etsi- että Korvaa -menetelmiä helposti VBA: n avulla. Nämä sisäänrakennetut menetelmät ovat paljon nopeampia kuin mikään muu, jonka voisit kirjoittaa itse VBA: han.
Etsi VBA -esimerkki
Etsintätoiminnon osoittamiseksi loimme Sheet1: ssä seuraavan tietojoukon.
Jos haluat seurata, kirjoita tiedot omaan työkirjaasi.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA -haku ilman valinnaisia parametreja
Kun käytät VBA Find -menetelmää, voit asettaa monia valinnaisia parametreja.
Suosittelemme vahvasti kaikkien parametrien määrittämistä aina, kun käytät etsintämenetelmää!
Jos et määritä valinnaisia parametreja, VBA käyttää parhaillaan valittuja parametreja Excelin Etsi -ikkunassa. Tämä tarkoittaa, ettet ehkä tiedä, mitä hakuparametreja käytetään koodin suorittamisen aikana. Haku voidaan suorittaa koko työkirjassa tai taulukossa. Se voisi etsiä kaavoja tai arvoja. Sitä ei voi tietää, ellet tarkista manuaalisesti, mitä Excelin hakuikkunassa on valittu.
Yksinkertaisuuden vuoksi aloitamme esimerkillä ilman valinnaisia parametreja.
Esimerkki yksinkertaisesta etsinnästä
Katsotaanpa yksinkertaista Find -esimerkkiä:
| 123456789 | Sub TestFind ()Dim MyRange alueenaAseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("työntekijä")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowEnd Sub |
Tämä koodi etsii "työntekijää" taulukon 1 käytetystä alueesta. Jos se löytää "työntekijän", se määrittää ensimmäisen löydetyn alueen vaihteluvälille MyRange.
Seuraavaksi näytetään viestiruudut, joissa on löydetyn tekstin osoite, sarake ja rivi.
Tässä esimerkissä käytetään oletushakuasetuksia (olettaen, että niitä ei ole muutettu Excelin hakuikkunassa):
- Hakuteksti vastaa osittain solun arvoa (tarkkaa solun vastaavuutta ei vaadita)
- Haku ei erota kirjainkokoa.
- Etsi vain hakuja yhdestä laskentataulukosta
Näitä asetuksia voidaan muuttaa useilla valinnaisilla parametreilla (käsitellään alla).
Etsi menetelmähuomautuksia
- Etsi ei valitse solua, josta teksti löytyy. Se tunnistaa vain löydetyn alueen, jota voit käsitellä koodissasi.
- Etsi -menetelmä etsii vain ensimmäisen löydetyn esiintymän.
- Voit käyttää jokerimerkkejä (*) esim. hae "E*"
Mitään ei löytynyt
Jos hakutekstiä ei ole, alueobjekti pysyy tyhjänä. Tämä aiheuttaa suuren ongelman, kun koodisi yrittää näyttää sijaintiarvot, koska niitä ei ole. Tämä johtaa virheilmoitukseen, jota et halua.
Onneksi voit testata tyhjän alueen objektin VBA: ssa Is Operatorilla:
| 1 | Jos ei MyRange ei ole mitään sitten |
Koodin lisääminen edelliseen esimerkkiimme:
| 12345678910111213 | Sub TestFind ()Dim MyRange alueenaAseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("työntekijä")Jos ei MyRange ei ole mitään sittenMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowMuuMsgBox "Ei löydy"Loppu JosEnd Sub |
Etsi parametrit
Toistaiseksi olemme tarkastelleet vain perusesimerkkiä Find -menetelmän käytöstä. On kuitenkin olemassa useita valinnaisia parametreja, joiden avulla voit tarkentaa hakua
| Parametri | Tyyppi | Kuvaus | Arvot |
| Mitä | Vaaditaan | Etsi arvo | Mikä tahansa tietotyyppi, kuten merkkijono tai numeerinen |
| Jälkeen | Valinnainen | Yksisoluinen viittaus haun aloittamiseen | Solun osoite |
| Katso sisään | Valinnainen | Käytä hakuun kaavoja, arvoja ja kommentteja | xlArvot, xlKaavat, xlKommentit |
| Katso | Valinnainen | Sovita solun osa tai koko | xlKoko, xl Osa |
| SearchOrder | Valinnainen | Hakujärjestys - rivit tai sarakkeet | xlByRows, xlByColummns |
| SearchDirection | Valinnainen | Suunta hakua varten - eteen- tai taaksepäin | xlSeuraava, xl |
| MatchCase | Valinnainen | Haku eroaa kirjainkoosta tai ei | Totta vai tarua |
| MatchByte | Valinnainen | Käytetään vain, jos olet asentanut kaksitavuisen kielituen, esim. Kiinan kieli | Totta vai tarua |
| SearchFormat | Valinnainen | Salli haku solun muodon mukaan | Totta vai tarua |
Parametrin jälkeen ja Etsi useita arvoja
Käytät Parametrin jälkeen haun aloitussolun määrittämiseksi. Tästä on hyötyä, kun etsimääsi arvoa on useampi kuin yksi.
Jos haku on jo löytänyt yhden arvon ja tiedät, että arvoja löytyy enemmän, käytä ensimmäisen menetelmän tallentamiseen Etsi -menetelmää ja After -parametria ja käytä sitten tätä solua seuraavan haun lähtökohtana.
Tämän avulla voit etsiä useita hakutekstin esiintymiä:
| 123456789101112131415161718192021222324252627282930313233343536 | AlatestiMultipleFinds ()Dim MyRange as Range, OldRange as Range, FindStr as String"Etsi ensimmäinen esiintymä" "Valo ja lämpö" "Aseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")'Jos ei löydy, poistuJos MyRange ei ole mitään, sulje Sub'Näytön ensimmäinen osoite löytyiMsgBox MyRange.Address'Tee kopio alueobjektistaAseta OldRange = MyRange'Lisää osoite merkkijonoon, joka rajaa "|" merkkiFindStr = FindStr & "|" & MyRange.Adress'Toista valikoimaa ja etsi muita tapauksiaTehdäEtsi "Light & Heat" käyttämällä edelliseksi löydettyä osoitetta After -parametrinaAseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Jos osoite on jo löydetty, poistu do -silmukasta - tämä lopettaa jatkuvan silmukoinninJos InStr (FindStr, MyRange.Address), sulje sitten Do'Näytä viimeisin löydetty osoiteMsgBox MyRange.Address'Lisää uusin osoite osoitejonoonFindStr = FindStr & "|" & MyRange.Adress'kopioi nykyinen alueAseta OldRange = MyRangeSilmukkaEnd Sub |
Tämä koodi toistuu käytetyn alueen läpi ja näyttää osoitteen aina, kun se löytää "Light & Heat" -ilmentymän
Huomaa, että koodi jatkaa silmukointia, kunnes FindStr: stä löytyy päällekkäinen osoite, jolloin se poistuu Do -silmukasta.
Katso parametrista
Voit käyttää LookIn -parametri voit määrittää, mistä solun osasta haluat hakea. Voit määrittää solussa arvoja, kaavoja tai kommentteja.
- xlValues - Etsii soluarvoja (solun lopullinen arvo laskennan jälkeen)
- xlKaavat - haut itse solukaavassa (mitä tahansa soluun syötetään)
- xlKommentit - Etsii solmuistiinpanoja
- xlCommentsThreaded - Haku solukommenttien sisällä
Olettaen, että laskentataulukkoon on syötetty kaava, voit käyttää tämän esimerkkikoodin avulla minkä tahansa kaavan ensimmäisen sijainnin:
| 12345678910 | AlatestiLookIn ()Dim MyRange alueenaAseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Jos ei MyRange ei ole mitään sittenMsgBox MyRange.AddressMuuMsgBox "Ei löydy"Loppu JosEnd Sub |
Jos LookIn -parametrin arvoksi on asetettu xlValues, koodi näyttää "Not Found" -viestin. Tässä esimerkissä se palauttaa B10.
LookAt -parametrin käyttäminen
The LookAt -parametri määrittää, etsiikö etsintä tarkkaa solujen vastaavuutta vai etsikö mitä tahansa solua, joka sisältää hakuarvon.
- xlKoko - Edellyttää, että koko solu vastaa hakuarvoa
- xlOsa - Etsii hakumerkkijonosta solusta
Tämä koodiesimerkki etsii ensimmäisen solun, joka sisältää tekstin "valo". Kanssa Katso: = xlPart, se palaa otteluun "Light & Heat".
| 123456789 | AlatestiLookAt ()Dim MyRange alueenaAseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Jos ei MyRange ei ole mitään sittenMsgBox MyRange.AddressMuuMsgBox "Ei löydy"Loppu JosEnd Sub |
Jos xlKoko oli asetettu, vastaavuus palautuu vain, jos solun arvo on ”kevyt”.
SearchOrder -parametri
The SearchOrder -parametri määrää, miten haku suoritetaan koko alueella.
- xlRows - Haku suoritetaan rivi kerrallaan
- xlXolumns - Haku suoritetaan sarake sarakkeelta
| 123456789 | AlatestiSearchOrder ()Dim MyRange alueenaAseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("työntekijä", SearchOrder: = xlColumns)Jos ei MyRange ei ole mitään sittenMsgBox MyRange.AddressMuuMsgBox "Ei löydy"Loppu JosEnd Sub |
Tämä vaikuttaa siihen, mikä osuma löydetään ensin.
Käyttämällä aiemmin laskentataulukkoon syötettyjä testitietoja, kun hakujärjestys on sarakkeita, sijoitettu solu on A5. Kun hakujärjestysparametriksi muutetaan xlRows, sijaittava solu on C4
Tämä on tärkeää, jos hakualueella on päällekkäisiä arvoja ja haluat löytää ensimmäisen esiintymän tietyn sarakkeen nimen alla.
SearchDirection -parametri
The SearchDirection -parametri sanelee, mihin suuntaan haku tulee - tehokkaasti eteen- tai taaksepäin.
- xlSeuraava - Etsi seuraava vastaava arvo alueelta
- xlEdellinen - Hae aikaisempaa vastaavaa arvoa alueelta
Jälleen, jos hakualueella on päällekkäisiä arvoja, sillä voi olla vaikutus siihen, kumpi arvo löytyy ensin.
| 12345678910 | AlatestiSearchDirection ()Dim MyRange alueenaAseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Jos ei MyRange ei ole mitään sittenMsgBox MyRange.AddressMuuMsgBox "Ei löydy"Loppu JosEnd Sub |
Käyttämällä tätä koodia testitiedoissa hakusuunta xlPrevious palauttaa sijainnin C9. Parametrin xlNext käyttäminen palauttaa sijainnin A4.
Seuraava-parametri tarkoittaa, että haku alkaa hakualueen vasemmasta yläkulmasta ja toimii alaspäin. Edellinen parametri tarkoittaa, että haku alkaa hakualueen oikeasta alakulmasta ja toimii ylöspäin.
MatchByte -parametri
The MatchBye -parametri käytetään vain kielillä, jotka käyttävät kaksitavuisia merkkejä, kuten kiina, venäjä ja japani.
Jos tämän parametrin arvoksi on asetettu 'Tosi', Find etsii vain kaksitavuisia merkkejä ja kaksitavuisia merkkejä. Jos parametrin arvoksi on asetettu False, kaksitavuinen merkki vastaa yhden tai kahden tavun merkkiä.
SearchFormat -parametri
The SearchFormat -parametri voit etsiä sopivia solumuotoja. Tämä voi olla jokin käytetty fontti, lihavoitu kirjasin tai tekstin väri. Ennen kuin käytät tätä parametria, sinun on määritettävä haun vaadittava muoto Application.FindFormat -ominaisuuden avulla.
Tässä on esimerkki sen käytöstä:
| 12345678910111213 | AlatestiSearchFormat ()Dim MyRange alueenaApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TottaAseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Jos ei MyRange ei ole mitään sittenMsgBox MyRange.AddressMuuMsgBox "Ei löydy"Loppu JosApplication.FindFormat.ClearEnd Sub |
Tässä esimerkissä FindFormat ominaisuus on asetettu etsimään lihavoitua fonttia. Etsi -lause etsii sitten sanaa "lämpö" ja asettaa SearchFormat -parametrin arvoksi Tosi, niin että se palauttaa kyseisen tekstin vain, jos kirjasin on lihavoitu.
Aiemmin näytetyissä laskentataulukon tiedoissa tämä palauttaa A9, joka on ainoa solu, joka sisältää sanan "lämpö" lihavoituna.
Varmista, että FindFormat -ominaisuus on tyhjennetty koodin lopussa. Jos et tee sitä, seuraava haku ottaa silti tämän huomioon ja palauttaa vääriä tuloksia.
Jos käytät SearchFormat -parametria, voit käyttää myös yleismerkkiä (*) hakuarvona. Tässä tapauksessa se etsii mitä tahansa arvoa lihavoitulla fontilla:
| 1 | Aseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Useiden parametrien käyttö
Kaikkia tässä kuvattuja hakuparametreja voidaan tarvittaessa käyttää yhdessä toistensa kanssa.
Voit esimerkiksi yhdistää LookIn-parametrin MatchCase-parametriin, jotta voit tarkastella koko solutekstiä, mutta se erottaa kirjaimet ja kirjaimet
| 123456789 | AlatestiMultipleParameters ()Dim MyRange alueenaAseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Jos ei MyRange ei ole mitään sittenMsgBox MyRange.AddressMuuMsgBox "Ei löydy"Loppu JosEnd Sub |
Tässä esimerkissä koodi palauttaa A4, mutta jos käytimme vain osaa tekstistä, esim. "Lämpöä", mitään ei löydy, koska vastaamme koko solun arvoa. Lisäksi se epäonnistuu, koska kotelo ei täsmää.
| 1 | Aseta MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Korvaa Excel VBA: ssa
Kuten voit odottaa, Excel VBA: ssa on Korvaa -toiminto, joka toimii hyvin samalla tavalla kuin Etsi, mutta korvaa löydetyn solun sijainnin arvot uudella arvolla.
Nämä ovat parametrit, joita voit käyttää Replace method -lausekkeessa. Nämä toimivat täsmälleen samalla tavalla kuin Find -menetelmällä. Ainoa ero Findin kanssa on, että sinun on määritettävä korvaava parametri.
| Nimi | Tyyppi | Kuvaus | Arvot |
| Mitä | Vaaditaan | Etsi arvo | Mikä tahansa tietotyyppi, kuten merkkijono tai numeerinen |
| Korvaus | Vaaditaan | Korvaava merkkijono. | Mikä tahansa tietotyyppi, kuten merkkijono tai numeerinen |
| Katso | Valinnainen | Sovita solun osa tai koko | xlPart tai xlWhole |
| SearchOrder | Valinnainen | Hakujärjestys - rivit tai sarakkeet | xlByRows tai xlByColumns |
| MatchCase | Valinnainen | Haku eroaa kirjainkoosta tai ei | Totta vai tarua |
| MatchByte | Valinnainen | Käytetään vain, jos olet asentanut kaksitavuisen kielituen | Totta vai tarua |
| SearchFormat | Valinnainen | Salli haku solun muodon mukaan | Totta vai tarua |
| ReplaceFormat | Valinnainen | Menetelmän korvaava muoto. | Totta vai tarua |
Korvaa muoto -parametri etsii solua tietyssä muodossa, esim. lihavoitu samalla tavalla kuin SearchFormat -parametri toimii Find -menetelmässä. Sinun on ensin määritettävä Application.FindFormat -ominaisuus, kuten aiemmin näytetty Etsi esimerkkikoodi
Vaihda ilman valinnaisia parametreja
Yksinkertaisimmillaan sinun tarvitsee vain määrittää, mitä etsit ja millä haluat korvata sen.
| 123 | Sub TestReplace ()Sheets ("Sheet1"). UsedRange.Replace What: = "Light & Heat", Replacement: = "L & H"End Sub |
Huomaa, että Etsi -menetelmä palauttaa vain vastaavan arvon ensimmäisen esiintymän, kun taas Korvaa -menetelmä toimii koko määritetyn alueen läpi ja korvaa kaiken, josta se löytää osuman.
Tässä näkyvä korvauskoodi korvaa jokaisen Light & Heat -esiintymän L & H: lla koko UsedRange -objektin määrittämän solualueen läpi
VBA: n käyttäminen tekstin etsimiseen tai korvaamiseen VBA -tekstimerkkijonossa
Yllä olevat esimerkit toimivat hyvin, kun käytät VBA: ta vuorovaikutuksessa Excel -tietojen kanssa. Vuorovaikutuksessa VBA-merkkijonojen kanssa voit kuitenkin käyttää sisäänrakennettuja VBA-toimintoja, kuten INSTR ja REPLACE.
Voit käyttää INSTR -toiminto etsiäksesi merkkijonon pidemmästä merkkijonosta.
| 123 | Sub TestInstr ()MsgBox InStr ("Tämä on MyText -merkkijono", "MyText")End Sub |
Tämä esimerkkikoodi palauttaa arvon 9, joka on numeroasema, jossa "MyText" löytyy etsittävästä merkkijonosta.
Huomaa, että se erottaa isot ja pienet kirjaimet. Jos "MyText" on pieniä kirjaimia, arvo 0 palautetaan, mikä tarkoittaa, että hakumerkkijonoa ei löydy. Alla keskustellaan siitä, miten kirjainkoko voi poistaa käytöstä.
INSTR - Aloita
Saatavana on myös kaksi lisäparametria. Voit määrittää haun aloituspisteen:
| 1 | MsgBox InStr (9, "Tämä on MyText -merkkijono", "MyText") |
Aloituspiste on 9, joten se palauttaa silti 9. Jos aloituspiste oli 10, se palauttaisi 0 (ei ottelua), koska aloituskohta olisi liian kaukana eteenpäin.
INSTR - Kirjainkoon herkkyys
Voit myös asettaa Vertaa -parametrin arvoon vbBinaryCompare tai vbTextCompare. Jos asetat tämän parametrin, lausekkeessa on oltava aloitusparametrin arvo.
- vbBinaryCompare - Kirjainkoolla on väliä (oletus)
- vbTextCompare - Ei kirjainkoolla
| 1 | MsgBox InStr (1, "Tämä on tekstini merkkijono", "oma teksti", vbTextCompare) |
Tämä lausunto palauttaa silti 9, vaikka hakuteksti on pienillä kirjaimilla.
Voit poistaa kirjainkoon erottamisen käytöstä myös ilmoittamalla vaihtoehdon Vertaa tekstiä koodimoduulin yläosassa.
VBA -korvaustoiminto
Jos haluat korvata merkkijonon merkit eri koodilla, Korvaa -menetelmä on ihanteellinen tähän:
| 123 | Sub TestReplace ()MsgBox Replace ("This is MyText string", "MyText", "My Text")End Sub |
Tämä koodi korvaa 'MyText' ja 'My Text'. Huomaa, että hakumerkkijono erottaa isot ja pienet kirjaimet, koska binäärinen vertailu on oletusarvo.
Voit myös lisätä muita valinnaisia parametreja:
- alkaa - määrittää sijainnin alkuperäisessä merkkijonossa, josta korvaaminen on aloitettava. Toisin kuin Find -menetelmässä, se palauttaa katkaistun merkkijonon, joka alkaa Start -parametrin määrittämästä merkkiluvusta.
- Kreivi - määrittelee tehtävien vaihtojen määrän. Oletuksena Korvaa muuttaa kaikki löydetyn hakutekstin esiintymät, mutta voit rajoittaa tämän yksittäiseen korvaukseen asettamalla Count -parametrin arvoksi 1
- Vertailla - kuten Find -menetelmässä, voit määrittää binaarihaun tai tekstihaun käyttämällä vbBinaryCompare tai vbTextCompare. Binaari erottaa isot ja pienet kirjaimet
| 1 | MsgBox Replace ("Tämä on MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Tämä koodi palauttaa "Oma tekstimerkkijono (mytext)". Tämä johtuu siitä, että annettu aloituspiste on 9, joten uusi palautettu merkkijono alkaa merkistä 9. Vain ensimmäinen "MyText" on muutettu, koska Count -parametrin arvoksi on asetettu 1.
Replace -menetelmä on ihanteellinen ongelmien ratkaisemiseen, kuten ihmisten nimet, jotka sisältävät apostrofeja, esim. O'Flynn. Jos käytät yksittäisiä lainausmerkkejä merkkijonon arvon määrittämiseen ja siinä on heittomerkki, tämä aiheuttaa virheen, koska koodi tulkitsee apostrofin merkkijonon lopuksi eikä tunnista merkkijonon loppuosaa.
Voit käyttää korvausmenetelmää korvataksesi heittomerkit tyhjillä, poistamalla ne kokonaan.
