Ši pamoka parodys, kaip naudoti „Excel VBA“ paieškos ir pakeitimo metodus.
VBA Rasti
„Excel“ turi puikų integruotą Rasti ir Rasti ir pakeisti įrankiai.
Juos galima suaktyvinti naudojant sparčiuosius klavišus CTRL + F. (Rasti) arba CTRL + H (Pakeisti) arba per juostą: Pagrindinis> Redagavimas> Rasti ir pasirinkti.
Spustelėję Galimybės, galite pamatyti išplėstinės paieškos parinktis:
Naudodami VBA galite lengvai pasiekti ir rasti, ir pakeisti metodus. Šie integruoti metodai yra daug greitesni už viską, ką galėtumėte parašyti patys VBA.
Raskite VBA pavyzdį
Norėdami parodyti „Find“ funkciją, „Sheet1“ sukūrėme šį duomenų rinkinį.
Jei norite sekti, įveskite duomenis į savo darbo knygą.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
„VBA Find“ be pasirenkamų parametrų
Kai naudojate „VBA Find“ metodą, galite nustatyti daugybę pasirenkamų parametrų.
Mes primygtinai rekomenduojame apibrėžti visus parametrus, kai naudojate paieškos metodą!
Jei neapibrėžiate pasirenkamų parametrų, VBA naudos šiuo metu pasirinktus parametrus „Excel“ paieškos lange. Tai reiškia, kad galbūt nežinote, kokie paieškos parametrai naudojami vykdant kodą. Rasti gali būti vykdoma visoje darbaknygėje arba lape. Jis galėtų ieškoti formulių ar reikšmių. Negalite to sužinoti, nebent rankiniu būdu patikrinsite, kas šiuo metu pasirinkta „Excel“ paieškos lange.
Kad būtų paprasčiau, pradėsime nuo pavyzdžio, kuriame nenurodyti jokie pasirenkami parametrai.
Paprastas paieškos pavyzdys
Pažvelkime į paprastą paieškos pavyzdį:
| 123456789 | Sub TestFind ()„Dim MyRange“ kaip diapazonasNustatykite „MyRange = Sheets“ („Sheet1“). UsedRange.Find („darbuotojas“)„MsgBox MyRange“. Adresas„MsgBox MyRange.Column“„MsgBox MyRange.Row“Pabaiga Sub |
Šis kodas naudoja „1 darbuotojo“ diapazone esantį žodį „darbuotojas“. Jei jis randa „darbuotojas“, jis pirmą kartą rastą diapazoną priskiria diapazono kintamajam „MyRange“.
Tada bus rodomi pranešimų langeliai su rasto teksto adresu, stulpeliu ir eilute.
Šiame pavyzdyje naudojami numatytieji paieškos nustatymai (darant prielaidą, kad jie nebuvo pakeisti „Excel“ paieškos lange):
- Paieškos tekstas iš dalies atitinka langelio vertę (tiksli langelio atitiktis nebūtina)
- Paieška neskiria didžiųjų ir mažųjų raidžių.
- Rasti ieškoma tik viename darbalapyje
Šiuos nustatymus galima keisti naudojant įvairius pasirenkamus parametrus (aptarti toliau).
Rasti metodo pastabas
- Rasti nepasirenka langelio, kuriame rastas tekstas. Jis identifikuoja tik rastą diapazoną, kurį galite manipuliuoti savo kodu.
- Rasti metodas suras tik pirmą rastą egzempliorių.
- Galite naudoti pakaitos simbolius (*), pvz. ieškoti „E*“
Nieko nerasta
Jei paieškos teksto nėra, diapazono objektas liks tuščias. Tai sukelia didelę problemą, kai jūsų kodas bando rodyti vietovės vertes, nes jų nėra. Dėl to atsiras klaidos pranešimas, kurio nenorite.
Laimei, galite patikrinti tuščio diapazono objektą VBA naudodami „Is Operator“:
| 1 | Jei ne „MyRange“ yra niekas |
Pridėkite kodą prie ankstesnio pavyzdžio:
| 12345678910111213 | Sub TestFind ()„Dim MyRange“ kaip diapazonasNustatykite „MyRange = Sheets“ („Sheet1“). UsedRange.Find („darbuotojas“)Jei ne „MyRange“ yra niekas„MsgBox MyRange“. Adresas„MsgBox MyRange.Column“„MsgBox MyRange.Row“Kitas„MsgBox“ nerastaPabaiga JeiPabaiga Sub |
Raskite parametrus
Iki šiol mes apžvelgėme tik pagrindinį „Find“ metodo naudojimo pavyzdį. Tačiau yra keletas pasirenkamų parametrų, kurie padės patikslinti paiešką
| Parametras | Tipas | apibūdinimas | Vertybės |
| Ką | Būtina | Vertė, kurios reikia ieškoti | Bet koks duomenų tipas, pvz., Eilutė arba skaitinis |
| Po | Neprivaloma | Vienos langelio nuoroda norint pradėti paiešką | Langelio adresas |
| „LookIn“ | Neprivaloma | Paieškai naudokite formules, vertes, komentarus | xlValues, xlFormulas, xlKomentarai |
| Žiūrėti į | Neprivaloma | Suderinkite dalį ar visą langelį | xlVisa, xlDalis |
| SearchOrder | Neprivaloma | Užsakymas ieškoti eilutėse ar stulpeliuose | xlByRows, xlByColummns |
| SearchDirection | Neprivaloma | Paieškos kryptis - pirmyn arba atgal | xlKitas, xl |
| „MatchCase“ | Neprivaloma | Paieška skiria didžiąsias ir mažąsias raides | Tiesa ar melas |
| „MatchByte“ | Neprivaloma | Naudojama tik tuo atveju, jei įdiegėte dvigubo baito kalbos palaikymą, pvz. kinų kalba | Tiesa ar melas |
| „SearchFormat“ | Neprivaloma | Leisti paiešką pagal langelio formatą | Tiesa ar melas |
Po parametrų ir raskite kelias reikšmes
Jūs naudojate Po parametro norėdami nurodyti paieškos pradžios langelį. Tai naudinga, kai yra daugiau nei vienas ieškomos vertės pavyzdys.
Jei paieška jau rado vieną vertę ir žinote, kad bus rasta daugiau reikšmių, pirmam egzemplioriui įrašyti naudokite „Find“ metodą su parametru „After“ ir naudokite tą langelį kaip kitos paieškos pradžios tašką.
Tai galite naudoti norėdami rasti kelis paieškos teksto atvejus:
| 123456789101112131415161718192021222324252627282930313233343536 | Papildomas testasMultipleFinds ()„Dim MyRange“ kaip diapazonas, „OldRange“ kaip diapazonas, „FindStr“ kaip eilutė„Ieškokite pirmojo„ Šviesos ir šilumos “pavyzdžioNustatyti MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")„Jei nerandate, išeikiteJei „MyRange“ yra niekas, išeikite iš „Sub“„Rastas pirmasis rodomas adresas„MsgBox MyRange“. Adresas„Padarykite diapazono objekto kopijąNustatykite „OldRange“ = „MyRange“„Pridėkite adresą prie eilutės, skiriančios„ | “ charakterisFindStr = FindStr & "|" & MyRange. Adresas„Pakartokite diapazoną ir ieškokite kitų atvejųDaryk„Ieškokite„ Šviesa ir šiluma “naudodami ankstesnį rastą adresą kaip parametrą„ After “Nustatykite MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))„Jei adresas jau rastas, išeikite iš„ Do “ciklo - tai sustabdo nuolatinį cikląJei „InStr“ („FindStr“, „MyRange.Address“), tada uždarykite „Do“'Rodyti naujausią rastą adresą„MsgBox MyRange“. Adresas„Prie adresų eilutės pridėkite naujausią adresąFindStr = FindStr & "|" & MyRange. Adresas„pasidarykite dabartinio diapazono kopijąNustatykite „OldRange“ = „MyRange“KilpaPabaiga Sub |
Šis kodas kartosis per naudojamą diapazoną ir bus rodomas adresas kiekvieną kartą, kai bus rastas „Šviesos ir šilumos“ pavyzdys
Atminkite, kad kodas ir toliau kartosis, kol „FindStr“ bus rastas pasikartojantis adresas, tokiu atveju jis išeis iš „Do“ ciklo.
„LookIn“ parametras
Galite naudoti „LookIn“ parametras norėdami nurodyti, kuriame langelio komponente norite ieškoti. Langelyje galite nurodyti reikšmes, formules ar komentarus.
- xlValues - Ieškoma langelių reikšmių (galutinė langelio vertė po to, kai jis apskaičiuojamas)
- xlFormulos - Paieška pačioje langelio formulėje (kas įvesta į langelį)
- xlKomentarai - Paieškos langelio užrašuose
- xlCommentsThreaded - Paieškos langelio komentaruose
Darant prielaidą, kad darbalapyje buvo įvesta formulė, galite naudoti šį kodo pavyzdį, kad surastumėte pirmąją bet kurios formulės vietą:
| 12345678910 | Sub TestLookIn ()„Dim MyRange“ kaip diapazonasNustatykite MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Jei ne „MyRange“ yra niekas„MsgBox MyRange“. AdresasKitas„MsgBox“ nerastaPabaiga JeiPabaiga Sub |
Jei parametras „LookIn“ buvo nustatytas į „xlValues“, kodas rodys pranešimą „Nerasta“. Šiame pavyzdyje jis grąžins B10.
„LookAt“ parametro naudojimas
The „LookAt“ parametras nustato, ar radinys ieškos tikslios langelio atitikties, ar ieškos bet kurio langelio, kuriame yra paieškos vertė.
- xlVisa - Reikia, kad visas langelis atitiktų paieškos vertę
- xlDalis - Paieškos eilutės ieško langelyje
Šis kodo pavyzdys suras pirmąjį langelį, kuriame yra tekstas „šviesa“. Su Žiūrėti: = xlPart, tai grąžins rungtynes „Šviesa ir šiluma“.
| 123456789 | Antrasis testasLookAt ()„Dim MyRange“ kaip diapazonasNustatykite MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Jei ne „MyRange“ yra niekas„MsgBox MyRange“. AdresasKitas„MsgBox“ nerastaPabaiga JeiPabaiga Sub |
Jei xlVisa buvo nustatytas, atitiktis grįš tik tuo atveju, jei langelio vertė bus „šviesi“.
„SearchOrder“ parametras
The „SearchOrder“ parametras nurodo, kaip bus atliekama paieška visame diapazone.
- xlEilučių - Paieška atliekama eilutėmis
- xlXolumnai - Paieška atliekama po stulpelio
| 123456789 | Sub TestSearchOrder ()„Dim MyRange“ kaip diapazonasNustatykite MyRange = Sheets ("Sheet1"). UsedRange.Find ("darbuotojas", SearchOrder: = xlColumns)Jei ne „MyRange“ yra niekas„MsgBox MyRange“. AdresasKitas„MsgBox“ nerastaPabaiga JeiPabaiga Sub |
Tai įtakoja, kuri atitiktis bus rasta pirmiausia.
Naudojant anksčiau į darbalapį įvestus bandymo duomenis, kai paieškos tvarka yra stulpeliai, esantis langelis yra A5. Kai paieškos užsakymo parametras pakeičiamas į xlRows, ląstelė yra C4
Tai svarbu, jei paieškos diapazone yra pasikartojančių verčių ir norite rasti pirmąjį egzempliorių pagal tam tikrą stulpelio pavadinimą.
SearchDirection parametras
The „SearchDirection“ parametras nurodo, kuria kryptimi bus vykdoma paieška - veiksmingai pirmyn arba atgal.
- xlKitas - Ieškokite kitos atitinkančios vertės diapazone
- xl Ankstesnis - Ieškokite ankstesnės atitinkančios vertės diapazone
Vėlgi, jei paieškos diapazone yra pasikartojančių verčių, tai gali turėti įtakos tai, kuri iš jų yra pirmoji.
| 12345678910 | Sub TestSearchDirection ()„Dim MyRange“ kaip diapazonasNustatykite „MyRange = Sheets“ („Sheet1“). UsedRange.Find („Heat“, SearchDirection: = xlPrevious)Jei ne „MyRange“ yra niekas„MsgBox MyRange“. AdresasKitas„MsgBox“ nerastaPabaiga JeiPabaiga Sub |
Naudojant šį kodą bandymų duomenyse, „xlPrevious“ paieškos kryptis grąžins C9 vietą. Naudojant parametrą „xlNext“ bus grąžinta A4 vieta.
Kitas parametras reiškia, kad paieška prasidės viršutiniame kairiajame paieškos diapazono kampe ir veiks žemyn. Ankstesnis parametras reiškia, kad paieška prasidės apatiniame dešiniajame paieškos diapazono kampe ir veiks aukštyn.
„MatchByte“ parametras
The „MatchBye“ parametras naudojamas tik kalboms, kuriose kiekvienam simboliui žymėti naudojamas dvigubas baitas, pvz., kinų, rusų ir japonų.
Jei šis parametras nustatytas kaip „Tiesa“, tada „Find“ atitiks tik dvigubo baito simbolius su dvigubo baito simboliais. Jei parametras nustatytas kaip „Netiesa“, tada dviejų baitų simbolis sutaps su vieno ar dviejų baitų simboliais.
„SearchFormat“ parametras
The „SearchFormat“ parametras leidžia ieškoti atitinkamų langelių formatų. Tai gali būti tam tikras naudojamas šriftas, paryškintas šriftas arba teksto spalva. Prieš naudodami šį parametrą, turite nustatyti formatą, reikalingą paieškai naudojant „Application.FindFormat“ ypatybę.
Štai pavyzdys, kaip jį naudoti:
| 12345678910111213 | Papildomas testasSearchFormat ()„Dim MyRange“ kaip diapazonasApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TiesaNustatykite „MyRange = Sheets“ („Sheet1“). UsedRange.Find („heat“, Searchformat: = True)Jei ne „MyRange“ yra niekas„MsgBox MyRange“. AdresasKitas„MsgBox“ nerastaPabaiga JeiApplication.FindFormat.ClearPabaiga Sub |
Šiame pavyzdyje, „FindFormat“ nuosavybė nustatyta, kad būtų ieškomas paryškintas šriftas. Tada sakinys „Rasti“ ieško žodžio „šiluma“, nustatydamas „SearchFormat“ parametrą į „True“, kad jis grąžintų to teksto egzempliorių tik tuo atveju, jei šriftas yra paryškintas.
Anksčiau pateiktuose pavyzdiniuose darbalapio duomenyse bus pateiktas A9, kuris yra vienintelis langelis, kuriame yra paryškintas šriftas.
Įsitikinkite, kad „FindFormat“ ypatybė išvalyta kodo pabaigoje. Jei to nepadarysite, kita paieška vis tiek į tai atsižvelgs ir pateiks neteisingus rezultatus.
Kai naudojate parametrą „SearchFormat“, kaip paieškos reikšmę taip pat galite naudoti pakaitos simbolį (*). Tokiu atveju jis ieškos bet kokios vertės su paryškintu šriftu:
| 1 | Nustatykite MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Kelių parametrų naudojimas
Jei reikia, visi čia aptarti paieškos parametrai gali būti naudojami kartu.
Pvz., Galite sujungti parametrą „LookIn“ su parametru „MatchCase“, kad peržiūrėtumėte visą langelio tekstą, tačiau jis neskiria didžiųjų ir mažųjų raidžių
| 123456789 | Antrinis testas Multipleple Parameters ()„Dim MyRange“ kaip diapazonasNustatykite „MyRange = Sheets“ („Sheet1“). UsedRange.Find („Light & Heat“, LookAt: = xlWhole, MatchCase: = True)Jei ne „MyRange“ yra niekas„MsgBox MyRange“. AdresasKitas„MsgBox“ nerastaPabaiga JeiPabaiga Sub |
Šiame pavyzdyje kodas grąžins A4, bet jei naudosime tik dalį teksto, pvz. „Šiluma“, nieko nebūtų rasta, nes mes sutampa su visos ląstelės verte. Be to, tai nepavyktų dėl to, kad korpusas nesutampa.
| 1 | Nustatykite „MyRange = Sheets“ („Sheet1“). UsedRange.Find („heat“, LookAt: = xlWhole, MatchCase: = True) |
Pakeiskite „Excel VBA“
„Excel VBA“ yra, kaip galite tikėtis, funkcija „Pakeisti“, kuri veikia labai panašiai kaip „Rasti“, tačiau pakeičia rastos ląstelės vietos reikšmes nauja verte.
Tai yra parametrai, kuriuos galite naudoti pakeitimo metodo teiginyje. Jie veikia lygiai taip pat, kaip ir „Find“ metodo teiginys. Vienintelis skirtumas tarp „Rasti“ yra tas, kad turite nurodyti pakeitimo parametrą.
| vardas | Tipas | apibūdinimas | Vertybės |
| Ką | Būtina | Vertė, kurios reikia ieškoti | Bet koks duomenų tipas, pvz., Eilutė arba skaitinis |
| Pakeitimas | Būtina | Pakaitinė eilutė. | Bet koks duomenų tipas, pvz., Eilutė arba skaitinis |
| Žiūrėti į | Neprivaloma | Suderinkite dalį ar visą langelį | xlDalis arba xlVisa |
| SearchOrder | Neprivaloma | Paieškos tvarka - eilutės arba stulpeliai | xlByRows arba xlByColumns |
| „MatchCase“ | Neprivaloma | Paieška skiria didžiąsias ir mažąsias raides | Tiesa ar melas |
| „MatchByte“ | Neprivaloma | Naudojama tik tuo atveju, jei įdiegėte dvigubo baito kalbos palaikymą | Tiesa ar melas |
| „SearchFormat“ | Neprivaloma | Leisti paiešką pagal langelio formatą | Tiesa ar melas |
| ReplaceFormat | Neprivaloma | Metodo pakeitimo formatas. | Tiesa ar melas |
Parametras „Pakeisti formatą“ ieško langelio tam tikru formatu, pvz. paryškintas taip pat, kaip parametras „SearchFormat“ veikia „Find“ metodu. Pirmiausia turite nustatyti ypatybę „Application.FindFormat“, kaip parodyta anksčiau surastame pavyzdžio kodo paieškos pavyzdyje
Pakeiskite be pasirenkamų parametrų
Paprasčiausiai jums tereikia nurodyti, ko ieškote ir kuo norite jį pakeisti.
| 123 | Sub TestReplace ()Skaičiuoklės ("Sheet1"). UsedRange.Replace What: = "Light & Heat", Replacement: = "L & H"Pabaiga Sub |
Atminkite, kad „Rasti“ metodas grąžins tik pirmąją atitikties reikšmės egzempliorių, tuo tarpu pakeitimo metodas veikia per visą nurodytą diapazoną ir pakeičia viską, kur jis randa atitiktį.
Čia pateiktas pakeitimo kodas pakeis kiekvieną „Light & Heat“ egzempliorių su „L & H“ visame „UseRange“ objekto apibrėžtame ląstelių diapazone
VBA naudojimas tekstui rasti ar pakeisti VBA teksto eilutėje
Pirmiau pateikti pavyzdžiai puikiai tinka naudojant VBA sąveikaujant su „Excel“ duomenimis. Tačiau norėdami sąveikauti su VBA eilutėmis, galite naudoti integruotas VBA funkcijas, tokias kaip INSTR ir REPLACE.
Galite naudoti Funkcija INSTR norėdami rasti teksto eilutę ilgesnėje eilutėje.
| 123 | Antrinis testas Instr ()„MsgBox InStr“ („Tai yra mano teksto eilutė“, „Mano tekstas“)Pabaiga Sub |
Šis pavyzdinis kodas grąžins reikšmę 9, tai yra skaičiaus pozicija, kurioje ieškomoje eilutėje yra „MyText“.
Atminkite, kad tai yra didžiosios ir mažosios raidės. Jei „MyText“ yra mažosios raidės, grąžinama 0 reikšmė, o tai reiškia, kad paieškos eilutė nerasta. Žemiau aptarsime, kaip išjungti didžiųjų ir mažųjų raidžių jautrumą.
INSTR - Pradėti
Yra dar du pasirenkami parametrai. Galite nurodyti paieškos pradžios tašką:
| 1 | „MsgBox InStr“ (9, „Tai yra mano teksto eilutė“, „Mano tekstas“) |
Pradžios taškas yra nurodytas kaip 9, todėl jis vis tiek grįš 9. Jei pradžios taškas būtų 10, jis grąžintų 0 (neatitinka), nes pradžios taškas būtų per toli į priekį.
INSTR - didžiosios ir mažosios raidės
Taip pat galite nustatyti parametrą Palyginti vbBinaryCompare arba vbTextCompare. Jei nustatysite šį parametrą, teiginys turi turėti pradžios parametro vertę.
- vbBinaryCompare - didžiosios ir mažosios raidės (numatytasis)
- vbTextCompare - Neskiria didžiosios ir mažosios raidės
| 1 | „MsgBox InStr“ (1, „Tai yra mano teksto eilutė“, „mano tekstas“, „vbTextCompare“) |
Šis teiginys vis tiek grąžins 9, nors paieškos tekstas yra mažosiomis raidėmis.
Norėdami išjungti didžiųjų ir mažųjų raidžių didinimą, kodo modulio viršuje taip pat galite paskelbti parinktį Palyginti tekstą.
VBA pakeitimo funkcija
Jei norite pakeisti simbolius eilutėje su kitokiu tekstu savo kode, tada pakeitimo metodas idealiai tinka šiam tikslui:
| 123 | Sub TestReplace ()„MsgBox Replace“ („Tai yra mano teksto eilutė“, „Mano tekstas“, „Mano tekstas“)Pabaiga Sub |
Šis kodas pakeičia „MyText“ į „My Text“. Atminkite, kad paieškos eilutė skiria didžiąsias ir mažąsias raides, nes dvejetainis palyginimas yra numatytasis.
Taip pat galite pridėti kitų pasirenkamų parametrų:
- Pradėti - nustato poziciją pradinėje eilutėje, nuo kurios reikia pradėti pakeitimą. Skirtingai nuo metodo „Rasti“, jis grąžina sutrumpintą eilutę, pradedant simbolio skaičiumi, apibrėžtu parametru Pradėti.
- Grafas - nustato pakeitimų skaičių. Pagal numatytuosius nustatymus pakeitimas pakeis kiekvieną rastą paieškos teksto egzempliorių, tačiau jūs galite apsiriboti vienu pakeitimu, nustatydami parametrą Count į 1
- Palyginti - kaip ir ieškodami metodo, galite nurodyti dvejetainę paiešką arba teksto paiešką naudodami vbBinaryCompare arba vbTextCompare. Dvejetainis didžiosios ir mažosios raidės, o tekstas - didžiosios ir mažosios
| 1 | „MsgBox Replace“ („Tai yra mano teksto eilutė (mano tekstas)“, „Mano tekstas“, „Mano tekstas“, 9, 1, vbTextCompare) |
Šis kodas grąžina „Mano teksto eilutė (mano tekstas)“. Taip yra todėl, kad nurodytas pradžios taškas yra 9, taigi nauja grąžinta eilutė prasideda nuo 9 simbolio. Buvo pakeistas tik pirmasis „MyText“, nes parametras Count yra nustatytas į 1.
Pakeitimo metodas idealiai tinka spręsti tokias problemas kaip žmonių vardai, kuriuose yra apostrofų, pvz. O’Flynn. Jei eilutės reikšmei apibrėžti naudojate vienas kabutes ir yra apostrofas, tai sukels klaidą, nes kodas interpretuos apostrofą kaip eilutės pabaigą ir neatpažins likusios eilutės.
Galite naudoti pakeitimo metodą, kad apostrofas būtų pakeistas niekuo, jį visiškai pašalinus.
