Denne opplæringen vil demonstrere hvordan du bruker Find and Replace -metodene i Excel VBA.
VBA Finn
Excel har utmerket innebygd Finne og Finn og erstatt verktøy.
De kan aktiveres med snarveiene CTRL + F (Finn) eller CTRL + H (Erstatt) eller gjennom båndet: Hjem> Redigering> Finn og velg.
Ved å klikke Alternativer, kan du se avanserte søkealternativer:
Du kan enkelt få tilgang til både Finn og erstatt metodene ved å bruke VBA. Disse innebygde metodene er langt raskere enn noe du kan skrive selv i VBA.
Finn VBA -eksempel
For å demonstrere Find -funksjonaliteten opprettet vi følgende datasett i Sheet1.
Hvis du vil følge med, skriver du inn dataene i din egen arbeidsbok.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA Finn uten valgfrie parametere
Når du bruker VBA Find -metoden, er det mange valgfrie parametere du kan angi.
Vi anbefaler på det sterkeste å definere alle parametere når du bruker Find Method!
Hvis du ikke definerer de valgfrie parameterne, bruker VBA de valgte parameterne i Excel -vinduet Finn. Dette betyr at du kanskje ikke vet hvilke søkeparametere som brukes når koden kjøres. Find kan kjøres på hele arbeidsboken eller et ark. Det kan søke etter formler eller verdier. Det er ingen måte å vite, med mindre du manuelt kontrollerer hva som er valgt i Excel -vinduet Finn.
For enkelhets skyld starter vi med et eksempel uten valgfrie parametere.
Enkelt finne eksempel
La oss se på et enkelt Finn -eksempel:
| 123456789 | Sub TestFind ()Dim MyRange As RangeAngi MyRange = Sheets ("Sheet1"). UsedRange.Find ("ansatt")MsgBox MyRange.AdressMsgBox MyRange.ColumnMsgBox MyRange.RowSlutt Sub |
Denne koden søker etter "ansatt" i det brukte arkområdet1. Hvis den finner "ansatt", vil den tildele det første funnet området til områdevariabelen MyRange.
Deretter vises meldingsbokser med adressen, kolonnen og raden i teksten som er funnet.
I dette eksemplet brukes standard Finn -innstillinger (forutsatt at de ikke har blitt endret i Excel -vinduet Finn):
- Søketeksten er delvis tilpasset celleverdien (en eksakt cellematch er ikke nødvendig)
- Søket er ikke saksfølsomt.
- Finn bare søk i et enkelt regneark
Disse innstillingene kan endres med forskjellige valgfrie parametere (omtalt nedenfor).
Finn metodenotater
- Søk velger ikke cellen der teksten er funnet. Det identifiserer bare det funnet området, som du kan manipulere i koden din.
- Find -metoden finner bare den første forekomsten som ble funnet.
- Du kan bruke jokertegn (*) f.eks. søk etter 'E*'
Ingenting funnet
Hvis søketeksten ikke eksisterer, forblir områdeobjektet tomt. Dette forårsaker et stort problem når koden din prøver å vise posisjonsverdiene fordi de ikke eksisterer. Dette vil resultere i en feilmelding du ikke vil ha.
Heldigvis kan du teste for et tomt område i VBA ved å bruke Is Operator:
| 1 | Hvis ikke MyRange er ingenting da |
Legge til koden i vårt tidligere eksempel:
| 12345678910111213 | Sub TestFind ()Dim MyRange As RangeAngi MyRange = Sheets ("Sheet1"). UsedRange.Find ("ansatt")Hvis ikke MyRange er ingenting daMsgBox MyRange.AdressMsgBox MyRange.ColumnMsgBox MyRange.RowEllersMsgBox "Ikke funnet"Slutt omSlutt Sub |
Finn parametere
Så langt har vi bare sett på et grunnleggende eksempel på bruk av Find -metoden. Det finnes imidlertid en rekke valgfrie parametere som hjelper deg med å avgrense søket
| Parameter | Type | Beskrivelse | Verdier |
| Hva | Obligatorisk | Verdien du skal søke etter | Enhver datatype som en streng eller numerisk |
| Etter | Valgfri | Encellet referanse for å starte søket | Celleadresse |
| Se inn | Valgfri | Bruk formler, verdier, kommentarer for søk | xlValues, xlFormulas, xlComments |
| Se på | Valgfri | Match en del eller hele cellen | xlHele, xlDel |
| SearchOrder | Valgfri | Ordren om å søke i - rader eller kolonner | xlByRows, xlByColummns |
| SearchDirection | Valgfri | Retning for søk å gå inn - forover eller bakover | xlNeste, xlForrige |
| MatchCase | Valgfri | Søk er store og små bokstaver | Sant eller usant |
| MatchByte | Valgfri | Brukes bare hvis du har installert støtte for dobbel byte språk f.eks. kinesisk språk | Sant eller usant |
| Søkeformat | Valgfri | Tillat søk etter celleformat | Sant eller usant |
Etter parameter og finn flere verdier
Du bruker Etter parameter for å spesifisere startcellen for søket ditt. Dette er nyttig der det er mer enn én forekomst av verdien du søker etter.
Hvis et søk allerede har funnet en verdi og du vet at det vil bli funnet flere verdier, bruker du Find -metoden med parameteren ‘After’ for å registrere den første forekomsten og deretter bruke cellen som utgangspunkt for neste søk.
Du kan bruke dette til å finne flere forekomster av søketeksten:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Dim MyRange As Range, OldRange As Range, FindStr As String'Se etter første forekomst av' 'Light & Heat'Angi MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")'Hvis den ikke blir funnet, avsluttHvis MyRange ikke er noe, avslutt Sub'Vis første adresse funnetMsgBox MyRange.Adress'Lag en kopi av rekkeviddeobjektetSett OldRange = MyRange'Legg til adressen til strengen som avgrenser med "|" karakterFindStr = FindStr & "|" & MyRange.Adress'Iterate gjennom området på jakt etter andre forekomsterGjøre'Søk etter' Light & Heat 'ved å bruke den forrige funnet adressen som After -parameterenAngi MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Hvis adressen allerede er funnet, avslutt do -sløyfen - dette stopper kontinuerlig loopingHvis InStr (FindStr, MyRange.Address), avslutter du Do'Vis siste funnet adresseMsgBox MyRange.Adress'Legg til den siste adressen i strengen med adresserFindStr = FindStr & "|" & MyRange.Adress'ta en kopi av gjeldende områdeSett OldRange = MyRangeLøkkeSlutt Sub |
Denne koden vil gjenta gjennom det brukte området, og vil vise adressen hver gang den finner en forekomst av 'Light & Heat'
Vær oppmerksom på at koden fortsetter å løkke til en duplikatadresse blir funnet i FindStr, i så fall vil den gå ut av Do -løkken.
LookIn -parameter
Du kan bruke LookIn -parameter for å spesifisere hvilken komponent i cellen du vil søke i. Du kan angi verdier, formler eller kommentarer i en celle.
- xlVerdier - Søker celleverdier (sluttverdien til en celle etter beregning)
- xlFormler - Søker i selve celleformelen (det som er lagt inn i cellen)
- xlKommentarer - Søker i cellenotater
- xlCommentsThreaded - Søker i cellekommentarer
Forutsatt at en formel er angitt i regnearket, kan du bruke denne eksempelkoden til å finne den første plasseringen av en hvilken som helst formel:
| 12345678910 | Sub TestLookIn ()Dim MyRange As RangeAngi MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Hvis ikke MyRange er ingenting daMsgBox MyRange.AdressEllersMsgBox "Ikke funnet"Slutt omSlutt Sub |
Hvis "LookIn" -parameteren var satt til xlValues, ville koden vise en "Ikke funnet" -melding. I dette eksemplet vil det returnere B10.
Bruke LookAt -parameteren
De LookAt -parameter bestemmer om finn vil søke etter en eksakt cellematch, eller søke etter en celle som inneholder søkeverdien.
- xlHele - Krever at hele cellen samsvarer med søkeverdien
- xlDel - Søker i en celle etter søkestrengen
Dette kodeeksemplet finner den første cellen som inneholder teksten "lys". Med Lookat: = xlPart, det vil returnere en kamp for “Light & Heat”.
| 123456789 | Sub TestLookAt ()Dim MyRange As RangeAngi MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Hvis ikke MyRange er ingenting daMsgBox MyRange.AdressEllersMsgBox "Ikke funnet"Slutt omSlutt Sub |
Hvis xlHele ble angitt, ville en treff bare returnere hvis celleverdien var "lys".
SearchOrder Parameter
De SearchOrder -parameter dikterer hvordan søket vil bli utført i hele området.
- xlRader - Søk gjøres rad for rad
- xlXolumns - Søk gjøres kolonne for kolonne
| 123456789 | Sub TestSearchOrder ()Dim MyRange As RangeAngi MyRange = Sheets ("Sheet1"). UsedRange.Find ("ansatt", SearchOrder: = xlColumns)Hvis ikke MyRange er ingenting daMsgBox MyRange.AdressEllersMsgBox "Ikke funnet"Slutt omSlutt Sub |
Dette påvirker hvilken kamp som blir funnet først.
Ved å bruke testdataene som ble lagt inn i regnearket tidligere, når søkeordren er kolonner, er cellen A5. Når parameteren for søkeordre endres til xlRows, er cellen C4
Dette er viktig hvis du har like verdier innenfor søkeområdet, og du vil finne den første forekomsten under et bestemt kolonnenavn.
SearchDirection Parameter
De SearchDirection -parameter dikterer hvilken retning søket vil gå i - effektivt fremover eller bakover.
- xlNeste - Søk etter neste matchende verdi i området
- xlForrige - Søk etter tidligere matchende verdi i området
Igjen, hvis det er dupliserte verdier innenfor søkeområdet, kan det ha en effekt på hvilken som blir funnet først.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange As RangeAngi MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Hvis ikke MyRange er ingenting daMsgBox MyRange.AdressEllersMsgBox "Ikke funnet"Slutt omSlutt Sub |
Ved å bruke denne koden på testdataene, vil en søkeretning på xlPrevious returnere en posisjon på C9. Hvis du bruker xlNext -parameteren, returneres en plassering på A4.
Neste parameter betyr at søket vil begynne i øverste venstre hjørne av søkeområdet og fungere nedover. Den forrige parameteren betyr at søket vil starte i nedre høyre hjørne av søkeområdet og fungere oppover.
MatchByte -parameter
De MatchBye -parameter brukes bare for språk som bruker en dobbel byte for å representere hvert tegn, for eksempel kinesisk, russisk og japansk.
Hvis denne parameteren er satt til "True", vil Find bare matche dobbeltbyte-tegn med dobbeltbyte-tegn. Hvis parameteren er satt til 'False', vil et dobbeltbyte-tegn samsvare med enkelt- eller dobbeltbyte-tegn.
SearchFormat -parameter
De SearchFormat -parameter lar deg søke etter matchende celleformater. Dette kan være en bestemt skrift som brukes, eller en fet skrift, eller en tekstfarge. Før du bruker denne parameteren, må du angi formatet som kreves for søket ved å bruke egenskapen Application.FindFormat.
Her er et eksempel på hvordan du bruker det:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueAngi MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Hvis ikke MyRange er ingenting daMsgBox MyRange.AdressEllersMsgBox "Ikke funnet"Slutt omApplication.FindFormat.ClearSlutt Sub |
I dette eksemplet, FindFormat egenskapen er satt til å lete etter en fet skrift. Find -setningen søker deretter etter ordet 'varme' ved å sette SearchFormat -parameteren til True, slik at den bare returnerer en forekomst av teksten hvis skriften er fet.
I eksemplene på regnearkdataene som ble vist tidligere, vil dette returnere A9, som er den eneste cellen som inneholder ordet 'varme' i fet skrift.
Sørg for at FindFormat -egenskapen er slettet på slutten av koden. Hvis du ikke gjør det neste søket ditt, vil det fortsatt ta hensyn til dette og returnere feilaktige resultater.
Der du bruker en SearchFormat -parameter, kan du også bruke et jokertegn (*) som søkeverdi. I dette tilfellet vil den søke etter en hvilken som helst verdi med en fet skrift:
| 1 | Angi MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Bruke flere parametere
Alle søkeparametrene som diskuteres her kan brukes i kombinasjon med hverandre om nødvendig.
For eksempel kan du kombinere "LookIn" -parameteren med "MatchCase" -parameteren, slik at du ser på hele celleteksten, men det er store og små bokstaver
| 123456789 | Sub TestMultipleParameters ()Dim MyRange As RangeAngi MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Hvis ikke MyRange er ingenting daMsgBox MyRange.AdressEllersMsgBox "Ikke funnet"Slutt omSlutt Sub |
I dette eksemplet vil koden returnere A4, men hvis vi bare brukte en del av teksten f.eks. 'Varme', ville ingenting bli funnet fordi vi samsvarer med hele celleverdien. Det ville også mislykkes på grunn av at saken ikke samsvarer.
| 1 | Angi MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Erstatt i Excel VBA
Det er, som du kanskje forventer, en Erstatt -funksjon i Excel VBA, som fungerer på en lignende måte som "Finn", men erstatter verdiene på cellelokaliteten som er funnet med en ny verdi.
Dette er parametrene du kan bruke i en Erstatt metode -setning. Disse fungerer på nøyaktig samme måte som for Find method statement. Den eneste forskjellen på "Finn" er at du må spesifisere en erstatningsparameter.
| Navn | Type | Beskrivelse | Verdier |
| Hva | Obligatorisk | Verdien du skal søke etter | Enhver datatype som en streng eller numerisk |
| Erstatning | Obligatorisk | Erstatningsstrengen. | Enhver datatype som en streng eller numerisk |
| Se på | Valgfri | Match delen eller hele cellen | xlPart eller xlHele |
| SearchOrder | Valgfri | Rekkefølgen for å søke i - Rader eller kolonner | xlByRows eller xlByColumns |
| MatchCase | Valgfri | Søk er store og små bokstaver | Sant eller usant |
| MatchByte | Valgfri | Brukes bare hvis du har installert språkstøtte for dobbel byte | Sant eller usant |
| Søkeformat | Valgfri | Tillat søk etter celleformat | Sant eller usant |
| ReplaceFormat | Valgfri | Erstattformatet for metoden. | Sant eller usant |
Parameteren Erstatt format søker etter en celle med et bestemt format, f.eks. fet skrift på samme måte som parameteren SearchFormat fungerer i Find -metoden. Du må angi Application.FindFormat -egenskapen først, som vist i Finn eksempelkoden vist tidligere
Erstatt uten valgfrie parametere
På det enkleste trenger du bare å spesifisere hva du søker etter og hva du vil erstatte det med.
| 123 | Sub TestReplace ()Sheets ("Sheet1"). UsedRange.Replace What: = "Light & Heat", Replacement: = "L & H"Slutt Sub |
Vær oppmerksom på at Find -metoden bare vil returnere den første forekomsten av den matchede verdien, mens Replace -metoden fungerer gjennom hele det angitte området og erstatter alt den finner en samsvar med.
Erstatningskoden som vises her, vil erstatte hver forekomst av ‘Light & Heat’ med ‘L & H’ gjennom hele celleområdet definert av UsedRange -objektet
Bruke VBA til å finne eller erstatte tekst i en VBA -tekststreng
Eksemplene ovenfor fungerer utmerket når du bruker VBA til å samhandle med Excel -data. For å samhandle med VBA-strenger kan du imidlertid bruke innebygde VBA-funksjoner som INSTR og REPLACE.
Du kan bruke INSTR Funksjon for å finne en tekststreng i en lengre streng.
| 123 | Sub TestInstr ()MsgBox InStr ("Dette er MyText -streng", "MyText")Slutt Sub |
Denne eksempelkoden returnerer verdien 9, som er tallposisjonen der "MyText" er funnet i strengen som skal søkes.
Vær oppmerksom på at det er store og små bokstaver. Hvis "MyText" er små bokstaver, returneres verdien 0 som betyr at søkestrengen ikke ble funnet. Nedenfor vil vi diskutere hvordan du deaktiverer følsomhet mellom store og små bokstaver.
INSTR - Start
Det er ytterligere to valgfrie parametere tilgjengelig. Du kan angi startpunktet for søket:
| 1 | MsgBox InStr (9, "This is MyText string", "MyText") |
Startpunktet er spesifisert som 9, så det vil fortsatt returnere 9. Hvis startpunktet var 10, ville det returnere 0 (ingen kamp) ettersom startpunktet ville være for langt fremover.
INSTR - Case Sensitivity
Du kan også sette en sammenligningsparameter til vbBinaryCompare eller vbTextCompare. Hvis du angir denne parameteren, må setningen ha en startparameterverdi.
- vbBinaryCompare - Skille mellom store og små bokstaver (standard)
- vbTextCompare - Ikke store og små bokstaver
| 1 | MsgBox InStr (1, "This is MyText string", "mytext", vbTextCompare) |
Denne uttalelsen vil fortsatt returnere 9, selv om søketeksten er med små bokstaver.
For å deaktivere store og små bokstaver, kan du også erklære Alternativ sammenligne tekst øverst i kodemodulen.
VBA Erstatt funksjon
Hvis du ønsker å erstatte tegn i en streng med annen tekst i koden din, er erstatningsmetoden ideell for dette:
| 123 | Sub TestReplace ()MsgBox Replace ("This is MyText string", "MyText", "My Text")Slutt Sub |
Denne koden erstatter 'MyText' med 'My Text'. Vær oppmerksom på at søkestrengen er store og små bokstaver, ettersom en binær sammenligning er standard.
Du kan også legge til andre valgfrie parametere:
- Start - definerer posisjon i startstrengen som erstatningen må starte fra. I motsetning til Find -metoden returnerer den en avkortet streng som starter fra tegnetallet definert av Start -parameteren.
- Telle - definerer antall erstatninger som skal utføres. Som standard vil Replace endre hver forekomst av søketeksten som blir funnet, men du kan begrense dette til en enkelt erstatning ved å sette Count -parameteren til 1
- Sammenligne - som i Find -metoden kan du spesifisere et binært søk eller et tekstsøk ved hjelp av vbBinaryCompare eller vbTextCompare. Binær er store og små bokstaver
| 1 | MsgBox Replace ("This is MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Denne koden returnerer "Min tekststreng (mytext)". Dette er fordi oppgitt startpunkt er 9, så den nye returnerte strengen starter med tegnet 9. Bare den første "MyText" har blitt endret fordi Count -parameteren er satt til 1.
Erstatt -metoden er ideell for å løse problemer som folks navn som inneholder apostrofer f.eks. O'Flynn. Hvis du bruker enkle anførselstegn for å definere en strengverdi og det er en apostrof, vil dette forårsake en feil fordi koden vil tolke apostrofen som enden av strengen og ikke vil gjenkjenne resten av strengen.
Du kan bruke Erstatt -metoden for å erstatte apostrofen med ingenting, og fjerne den helt.
