Denne vejledning viser, hvordan du bruger Find og erstat -metoderne i Excel VBA.
VBA Find
Excel har fremragende indbygget Find og Find og udskift værktøjer.
De kan aktiveres med genvejene CTRL + F (Find) eller CTRL + H (Erstat) eller gennem båndet: Hjem> Redigering> Find og vælg.
Ved at klikke Muligheder, kan du se avancerede søgemuligheder:
Du kan nemt få adgang til både Find og Udskift -metoderne ved hjælp af VBA. Disse indbyggede metoder er langt hurtigere end noget, du selv kunne skrive i VBA.
Find VBA -eksempel
For at demonstrere Find -funktionaliteten oprettede vi følgende datasæt i Sheet1.
Hvis du gerne vil følge med, skal du indtaste dataene i din egen projektmappe.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA Find uden valgfri parametre
Når du bruger VBA Find -metoden, er der mange valgfrie parametre, som du kan angive.
Vi anbefaler kraftigt at definere alle parametre, når du bruger Find -metoden!
Hvis du ikke definerer de valgfrie parametre, bruger VBA de aktuelt valgte parametre i Excel -vinduet Find. Det betyder, at du måske ikke ved, hvilke søgeparametre der bruges, når koden køres. Find kunne køres på hele projektmappen eller et ark. Det kan søge efter formler eller værdier. Der er ingen måde at vide, medmindre du manuelt kontrollerer, hvad der aktuelt er valgt i Excel's vindue Find.
For enkelheds skyld starter vi med et eksempel uden valgfrie parametre defineret.
Enkelt Find Eksempel
Lad os se på et enkelt Find -eksempel:
| 123456789 | Sub TestFind ()Dim MyRange As RangeIndstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("medarbejder")MsgBox MyRange.AdressMsgBox MyRange.ColumnMsgBox MyRange.RowAfslut Sub |
Denne kode søger efter "medarbejder" i det anvendte arkområde1. Hvis den finder "medarbejder", vil den tildele det første fundne område til områdevariablen MyRange.
Derefter vises meddelelsesbokse med adressen, kolonnen og rækken af den fundne tekst.
I dette eksempel bruges standardindstillingerne for Find (forudsat at de ikke er blevet ændret i Excel -vinduet Find):
- Søgeteksten matches delvist med celleværdien (en nøjagtig cellematch er ikke påkrævet)
- Søgningen er ikke store og små bogstaver.
- Find søger kun i et enkelt regneark
Disse indstillinger kan ændres med forskellige valgfrie parametre (diskuteret nedenfor).
Find metodenotater
- Find vælger ikke den celle, hvor teksten findes. Det identificerer kun det fundne område, som du kan manipulere i din kode.
- Find -metoden finder kun den første forekommende forekomst.
- Du kan bruge jokertegn (*) f.eks. søg efter 'E*'
Intet fundet
Hvis søgeteksten ikke findes, forbliver områdeobjektet tomt. Dette forårsager et stort problem, når din kode forsøger at vise placeringsværdierne, fordi de ikke findes. Dette vil resultere i en fejlmeddelelse, som du ikke ønsker.
Heldigvis kan du teste for et tomt område inden for VBA ved hjælp af Is Operator:
| 1 | Hvis ikke MyRange er ingenting |
Tilføjelse af koden til vores tidligere eksempel:
| 12345678910111213 | Sub TestFind ()Dim MyRange As RangeIndstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("medarbejder")Hvis ikke MyRange er ingentingMsgBox MyRange.AdressMsgBox MyRange.ColumnMsgBox MyRange.RowAndetMsgBox "Ikke fundet"Afslut HvisAfslut Sub |
Find parametre
Indtil videre har vi kun set på et grundlæggende eksempel på at bruge Find -metoden. Der er dog en række valgfrie parametre til rådighed for at hjælpe dig med at forfine din søgning
| Parameter | Type | Beskrivelse | Værdier |
| Hvad | Påkrævet | Værdien at søge efter | Enhver datatype såsom en streng eller numerisk |
| Efter | Valgfri | Enkeltcellehenvisning for at starte din søgning | Celleadresse |
| Kig ind | Valgfri | Brug formler, værdier, kommentarer til søgning | xlVærdier, xlFormler, xlKommentarer |
| Se på | Valgfri | Match en del af eller en hel af en celle | xlHele, xlDel |
| SearchOrder | Valgfri | Ordren om at søge i - rækker eller kolonner | xlByRows, xlByColummns |
| SearchDirection | Valgfri | Retning for søgning at gå ind - frem eller tilbage | xlNæste, xlForrige |
| MatchCase | Valgfri | Søgning er store og små bogstaver | Sandt eller falsk |
| MatchByte | Valgfri | Anvendes kun hvis du har installeret understøttelse af dobbelt byte f.eks. kinesisk sprog | Sandt eller falsk |
| Søgeformat | Valgfri | Tillad søgning efter celleformat | Sandt eller falsk |
Efter parameter og find flere værdier
Du bruger Efter parameter for at angive startcellen til din søgning. Dette er nyttigt, når der er mere end én forekomst af den værdi, du søger efter.
Hvis en søgning allerede har fundet en værdi, og du ved, at der vil blive fundet flere værdier, bruger du Find -metoden med parameteren 'Efter' til at registrere den første forekomst og derefter bruge den celle som udgangspunkt for den næste søgning.
Du kan bruge dette til at finde flere forekomster af din søgetekst:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Dim MyRange As Range, OldRange As Range, FindStr As String'Se efter første forekomst af "' Light & Heat"Indstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")'Hvis den ikke findes, skal du afslutteHvis MyRange ikke er noget, skal du afslutte Sub'Vis den første adresse fundetMsgBox MyRange.Adress'Lav en kopi af områdeobjektetIndstil OldRange = MyRange'Føj adressen til den streng, der afgrænser med et "|" KarakterFindStr = FindStr & "|" & MyRange.Adress'Iterate gennem området på udkig efter andre tilfældeGør'Søg efter' Lys og varme 'ved hjælp af den tidligere fundne adresse som efterparameterenIndstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Hvis adressen allerede er fundet, skal du forlade do -loop - dette stopper kontinuerlig loopingHvis InStr (FindStr, MyRange.Address) derefter afslut Do'Vis senest fundet adresseMsgBox MyRange.Adress'Føj den nyeste adresse til rækken af adresserFindStr = FindStr & "|" & MyRange.Adress'lav en kopi af det aktuelle områdeIndstil OldRange = MyRangeSløjfeAfslut Sub |
Denne kode gentages gennem det anvendte område og viser adressen hver gang den finder en forekomst af 'Light & Heat'
Bemærk, at koden bliver ved med at løkke, indtil der findes en duplikatadresse i FindStr, i hvilket tilfælde den forlader Do -sløjfen.
LookIn -parameter
Du kan bruge LookIn parameter for at angive hvilken komponent i cellen du vil søge i. Du kan angive værdier, formler eller kommentarer i en celle.
- xlVærdier - Søger celleværdier (den endelige værdi af en celle efter beregning)
- xlFormler - Søger inden for selve celleformlen (hvad der end er indtastet i cellen)
- xlKommentarer - Søger inden for cellenoter
- xlKommentarer Gevind - Søger inden for cellekommentarer
Forudsat at der er angivet en formel på regnearket, kan du bruge denne eksempelkode til at finde den første placering af enhver formel:
| 12345678910 | Sub TestLookIn ()Dim MyRange As RangeIndstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Hvis ikke MyRange er ingentingMsgBox MyRange.AdressAndetMsgBox "Ikke fundet"Afslut HvisAfslut Sub |
Hvis parameteren ‘LookIn’ var indstillet til xlValues, ville koden vise en meddelelse ‘Ikke fundet’. I dette eksempel returnerer det B10.
Brug af LookAt -parameteren
Det LookAt -parameter bestemmer, om find vil søge efter et nøjagtigt cellematch eller søge efter en celle, der indeholder søgeværdien.
- xlHele - Kræver hele cellen for at matche søgeværdien
- xlDel - Søger inden for en celle efter søgestrengen
Dette kodeeksempel finder den første celle, der indeholder teksten "lys". Med Lookat: = xlPart, det vil returnere en kamp for “Light & Heat”.
| 123456789 | Sub TestLookAt ()Dim MyRange As RangeIndstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Hvis ikke MyRange er ingentingMsgBox MyRange.AdressAndetMsgBox "Ikke fundet"Afslut HvisAfslut Sub |
Hvis xlHele blev indstillet, ville en match kun vende tilbage, hvis celleværdien var "lys".
SearchOrder -parameter
Det SearchOrder -parameter dikterer, hvordan søgningen vil blive udført i hele området.
- xlRækker - Søgning udføres række for række
- xlXolumns - Søgning udføres kolonne for kolonne
| 123456789 | Sub TestSearchOrder ()Dim MyRange As RangeIndstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("medarbejder", SearchOrder: = xlColumns)Hvis ikke MyRange er ingentingMsgBox MyRange.AdressAndetMsgBox "Ikke fundet"Afslut HvisAfslut Sub |
Dette påvirker, hvilken match der vil blive fundet først.
Ved hjælp af de testdata, der er indtastet i regnearket tidligere, når søgerækkefølgen er kolonner, er den lokaliserede celle A5. Når parameteren for søgerækkefølge ændres til xlRows, er den lokaliserede celle C4
Dette er vigtigt, hvis du har dublerede værdier inden for søgeområdet, og du vil finde den første forekomst under et bestemt kolonnenavn.
SearchDirection Parameter
Det SearchDirection -parameter dikterer i hvilken retning søgningen vil gå - effektivt frem eller tilbage.
- xlNæste - Søg efter den næste matchende værdi i området
- xlForrige - Søg efter tidligere matchende værdi i området
Igen, hvis der er dublerede værdier inden for søgeområdet, kan det have en effekt, som man først finder.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange As RangeIndstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Hvis ikke MyRange er ingentingMsgBox MyRange.AdressAndetMsgBox "Ikke fundet"Afslut HvisAfslut Sub |
Ved hjælp af denne kode på testdataene vil en søgeretning på xlPrevious returnere en placering på C9. Brug af xlNext -parameteren returnerer en placering på A4.
Parameteren Næste betyder, at søgningen vil begynde i øverste venstre hjørne af søgeområdet og arbejde nedad. Den forrige parameter betyder, at søgningen starter i nederste højre hjørne af søgeområdet og fungerer opad.
MatchByte -parameter
Det MatchBye -parameter bruges kun til sprog, der bruger en dobbeltbyte til at repræsentere hvert tegn, såsom kinesisk, russisk og japansk.
Hvis denne parameter er indstillet til 'Sand', matcher Find kun dobbeltbyte-tegn med dobbeltbyte-tegn. Hvis parameteren er indstillet til 'Falsk', matcher et dobbeltbyte-tegn med enkelt- eller dobbeltbyte-tegn.
SearchFormat -parameter
Det SearchFormat -parameter giver dig mulighed for at søge efter matchende celleformater. Dette kan være en bestemt skrifttype, der bruges, eller en fed skrift eller en tekstfarve. Inden du bruger denne parameter, skal du angive det format, der kræves til søgningen ved hjælp af egenskaben Application.FindFormat.
Her er et eksempel på, hvordan du bruger det:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueIndstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Hvis ikke MyRange er ingentingMsgBox MyRange.AdressAndetMsgBox "Ikke fundet"Afslut HvisApplication.FindFormat.ClearAfslut Sub |
I dette eksempel er FindFormat egenskaben er sat til at lede efter en fed skrift. Find -sætningen søger derefter efter ordet 'varme', og indstiller parameteren SearchFormat til True, så den kun returnerer en forekomst af den tekst, hvis skrifttypen er fed.
I de tidligere viste eksempler på regnearkdata returnerer dette A9, som er den eneste celle, der indeholder ordet 'varme' med fed skrift.
Sørg for, at egenskaben FindFormat er slettet i slutningen af koden. Hvis du ikke gør din næste søgning, vil den stadig tage højde for dette og returnere forkerte resultater.
Hvor du bruger en SearchFormat -parameter, kan du også bruge et jokertegn (*) som søgeværdi. I dette tilfælde vil den søge efter en hvilken som helst værdi med en fed skrift:
| 1 | Indstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Brug af flere parametre
Alle de søgeparametre, der diskuteres her, kan bruges i kombination med hinanden, hvis det kræves.
For eksempel kan du kombinere parameteren ‘LookIn’ med parameteren ‘MatchCase’, så du ser på hele celleteksten, men det er store og små bogstaver
| 123456789 | Sub TestMultipleParameters ()Dim MyRange As RangeIndstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Hvis ikke MyRange er ingentingMsgBox MyRange.AdressAndetMsgBox "Ikke fundet"Afslut HvisAfslut Sub |
I dette eksempel returnerer koden A4, men hvis vi kun brugte en del af teksten f.eks. 'Varme', ville der ikke blive fundet noget, fordi vi matcher hele celleværdien. Det ville også mislykkes på grund af, at sagen ikke matchede.
| 1 | Indstil MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Udskift i Excel VBA
Der er, som du måske forventer, en Erstat -funktion i Excel VBA, som fungerer på en meget lignende måde som ‘Find’, men erstatter værdierne på cellens placering med en ny værdi.
Dette er de parametre, du kan bruge i en Erstat metode -erklæring. Disse fungerer på nøjagtig samme måde som for Find method statement. Den eneste forskel ved 'Find' er, at du skal angive en erstatningsparameter.
| Navn | Type | Beskrivelse | Værdier |
| Hvad | Påkrævet | Værdien at søge efter | Enhver datatype såsom en streng eller numerisk |
| Udskiftning | Påkrævet | Erstatningsstrengen. | Enhver datatype såsom en streng eller numerisk |
| Se på | Valgfri | Match en del eller hele cellen | xlPart eller xlHele |
| SearchOrder | Valgfri | Rækkefølgen til at søge i - Rækker eller kolonner | xlByRows eller xlByColumns |
| MatchCase | Valgfri | Søgning er store og små bogstaver | Sandt eller falsk |
| MatchByte | Valgfri | Bruges kun, hvis du har installeret understøttelse af dobbeltbyte | Sandt eller falsk |
| Søgeformat | Valgfri | Tillad søgning efter celleformat | Sandt eller falsk |
| ReplaceFormat | Valgfri | Erstat formatet for metoden. | Sandt eller falsk |
Parameteren Erstat format søger efter en celle med et bestemt format f.eks. fed på samme måde, som SearchFormat -parameteren fungerer i Find -metoden. Du skal først angive egenskaben Application.FindFormat som vist i Find eksempelkoden vist tidligere
Udskift uden valgfri parametre
På det enkleste skal du kun angive, hvad du leder efter, og hvad du vil erstatte det med.
| 123 | Sub TestReplace ()Ark ("Sheet1"). UsedRange.Replace What: = "Light & Heat", Replacement: = "L & H"Afslut Sub |
Bemærk, at Find -metoden kun returnerer den første forekomst af den matchede værdi, mens Replace -metoden fungerer gennem hele det angivne område og erstatter alt, hvad den finder et match på.
Udskiftningskoden vist her vil erstatte hver forekomst af 'Light & Heat' med 'L & H' gennem hele celleområdet defineret af UsedRange -objektet
Brug af VBA til at finde eller erstatte tekst i en VBA -tekststreng
Ovenstående eksempler fungerer godt, når du bruger VBA til at interagere med Excel -data. For at interagere med VBA-strenge kan du dog bruge indbyggede VBA-funktioner som INSTR og REPLACE.
Du kan bruge INSTR -funktion at finde en tekststreng inden for en længere streng.
| 123 | Sub TestInstr ()MsgBox InStr ("Dette er MyText -streng", "MyText")Afslut Sub |
Denne eksempelkode returnerer værdien 9, hvilket er nummerplaceringen, hvor 'MyText' findes i den streng, der skal søges efter.
Bemærk, at det er store og små bogstaver. Hvis 'MyText' alle er små bogstaver, returneres værdien 0, hvilket betyder, at søgestrengen ikke blev fundet. Nedenfor vil vi diskutere, hvordan du deaktiverer følsomhed mellem store og små bogstaver.
INSTR - Start
Der er yderligere to valgfrie parametre til rådighed. Du kan angive startpunktet for søgningen:
| 1 | MsgBox InStr (9, "This is MyText string", "MyText") |
Startpunktet er angivet som 9, så det vil stadig vende tilbage 9. Hvis startpunktet var 10, ville det returnere 0 (ingen match), da startpunktet ville være for langt frem.
INSTR - Case Sensitivity
Du kan også indstille en Sammenlign parameter til vbBinaryCompare eller vbTextCompare. Hvis du indstiller denne parameter, skal sætningen have en startparameterværdi.
- vbBinaryCompare - Etui-følsom (standard)
- vbTextCompare - Ikke store og små bogstaver
| 1 | MsgBox InStr (1, "This is MyText string", "mytext", vbTextCompare) |
Denne erklæring vil stadig returnere 9, selvom søgeteksten er med små bogstaver.
For at deaktivere store og små bogstaver følsomhed kan du også erklære Option Compare Text øverst i dit kodemodul.
VBA Udskift funktion
Hvis du ønsker at erstatte tegn i en streng med anden tekst i din kode, er Metoden Erstat ideel til dette:
| 123 | Sub TestReplace ()MsgBox Replace ("This is MyText string", "MyText", "My Text")Afslut Sub |
Denne kode erstatter 'MyText' med 'My Text'. Bemærk, at søgestrengen er store og små bogstaver, da en binær sammenligning er standard.
Du kan også tilføje andre valgfrie parametre:
- Start - definerer position i den indledende streng, som udskiftningen skal starte fra. I modsætning til i Find -metoden returnerer den en afkortet streng, der starter fra det tegnnummer, der er defineret af parameteren Start.
- Tælle - definerer antallet af udskiftninger, der skal foretages. Som standard ændrer Erstat hver forekomst af den søgte tekst, der findes, men du kan begrænse dette til en enkelt udskiftning ved at indstille parameteren Count til 1
- Sammenligne - som i Find -metoden kan du angive en binær søgning eller en tekstsøgning vha vbBinaryCompare eller vbTextCompare. Binære er store og små bogstaver følsomme
| 1 | MsgBox Replace ("This is MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Denne kode returnerer 'My Text string (mytext)'. Dette skyldes, at det angivne startpunkt er 9, så den nye returnerede streng starter med tegnet 9. Kun den første 'MyText' er blevet ændret, fordi parameteren Count er sat til 1.
Udskift -metoden er ideel til at løse problemer som folks navne, der indeholder apostrofer f.eks. O'Flynn. Hvis du bruger enkelte anførselstegn til at definere en strengværdi, og der er en apostrof, vil dette forårsage en fejl, fordi koden tolker apostrofen som enden af strengen og ikke genkender resten af strengen.
Du kan bruge erstatningsmetoden til at erstatte apostrofen med ingenting og fjerne den helt.
