Denna handledning kommer att visa hur du använder metoderna Sök och ersätt i Excel VBA.
VBA Hitta
Excel har utmärkt inbyggd Hitta och Hitta och ersätt verktyg.
De kan aktiveras med genvägarna CTRL + F (Hitta) eller CTRL + H (Ersätt) eller genom menyfliksområdet: Hem> Redigering> Hitta och välj.
Genom att klicka alternativ, kan du se avancerade sökalternativ:
Du kan enkelt komma åt både sök- och ersättningsmetoderna med VBA. Dessa inbyggda metoder är mycket snabbare än någonting du kan skriva själv i VBA.
Hitta VBA -exempel
För att demonstrera funktionen Sök skapade vi följande datamängd i Sheet1.
Om du vill följa med anger du data i din egen arbetsbok.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA Find utan valfria parametrar
När du använder VBA Find -metoden finns det många valfria parametrar som du kan ställa in.
Vi rekommenderar starkt att du definierar alla parametrar när du använder sökmetoden!
Om du inte definierar de valfria parametrarna använder VBA de valda parametrarna i Excel -fönstret Sök. Det betyder att du kanske inte vet vilka sökparametrar som används när koden körs. Sök kan köras på hela arbetsboken eller ett ark. Det kan söka efter formler eller värden. Det finns inget sätt att veta om du inte manuellt kontrollerar vad som för närvarande är valt i Excel -fönstret Sök.
För enkelhetens skull börjar vi med ett exempel utan valfria parametrar.
Enkelt hitta exempel
Låt oss titta på ett enkelt Hitta exempel:
| 123456789 | Sub TestFind ()Dim MyRange As RangeAnge MyRange = Sheets ("Sheet1"). UsedRange.Find ("anställd")MsgBox MyRange.AdressMsgBox MyRange.ColumnMsgBox MyRange.RowAvsluta Sub |
Den här koden söker efter "anställd" i det använda bladområdet1. Om den hittar "anställd" tilldelar den det först hittade intervallet till intervallvariabeln MyRange.
Därefter visas meddelanderutor med adressen, kolumnen och raden för den hittade texten.
I det här exemplet används standardinställningarna för Sök (förutsatt att de inte har ändrats i Excel -fönstret Sök):
- Söktexten matchas delvis med cellvärdet (en exakt cellmatchning krävs inte)
- Sökningen är inte skiftlägeskänslig.
- Sök bara efter ett enda kalkylblad
Dessa inställningar kan ändras med olika valfria parametrar (diskuteras nedan).
Hitta metodanteckningar
- Sök väljer inte cellen där texten finns. Det identifierar bara det hittade intervallet, som du kan manipulera i din kod.
- Sökmetoden hittar bara den första instansen som hittades.
- Du kan använda jokertecken (*) t.ex. sök efter 'E*'
inget hittat
Om söktexten inte existerar, förblir intervallobjektet tomt. Detta orsakar ett stort problem när din kod försöker visa platsvärdena eftersom de inte finns. Detta resulterar i ett felmeddelande som du inte vill ha.
Lyckligtvis kan du testa ett tomt område inom VBA med hjälp av Is Operator:
| 1 | Om inte MyRange är ingenting då |
Lägga till koden i vårt tidigare exempel:
| 12345678910111213 | Sub TestFind ()Dim MyRange As RangeAnge MyRange = Sheets ("Sheet1"). UsedRange.Find ("anställd")Om inte MyRange är ingenting dåMsgBox MyRange.AdressMsgBox MyRange.ColumnMsgBox MyRange.RowAnnanMsgBox "Hittades inte"Avsluta omAvsluta Sub |
Hitta parametrar
Hittills har vi bara tittat på ett grundläggande exempel på att använda metoden Find. Det finns dock ett antal valfria parametrar som hjälper dig att förfina din sökning
| Parameter | Typ | Beskrivning | Värden |
| Vad | Nödvändig | Värdet att söka efter | Vilken datatyp som helst som en sträng eller numerisk |
| Efter | Frivillig | Encellsreferens för att börja din sökning | Celladress |
| Titta in | Frivillig | Använd formler, värden, kommentarer för sökning | xlValues, xlFormulas, xlComments |
| Titta på | Frivillig | Matcha hela eller en del av en cell | xlHele, xlPart |
| SearchOrder | Frivillig | Ordern att söka i - rader eller kolumner | xlByRows, xlByColummns |
| SearchDirection | Frivillig | Riktning för sökning att gå in - framåt eller bakåt | xlNästa, xlFöregående |
| Liknande fall | Frivillig | Sökning är skiftlägeskänslig eller inte | Sant eller falskt |
| MatchByte | Frivillig | Används endast om du har installerat språkstöd för dubbel byte t.ex. kinesiskt språk | Sant eller falskt |
| SearchFormat | Frivillig | Tillåt sökning efter cellformat | Sant eller falskt |
Efter parameter och hitta flera värden
Du använder Efter parameter för att ange startcellen för din sökning. Detta är användbart om det finns mer än en instans av värdet du söker efter.
Om en sökning redan har hittat ett värde och du vet att det kommer att finnas fler värden, använder du metoden Sök med parametern 'Efter' för att spela in den första instansen och använder sedan den cellen som utgångspunkt för nästa sökning.
Du kan använda detta för att hitta flera instanser av din söktext:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Dim MyRange As Range, OldRange As Range, FindStr As String'Leta efter första instansen av' 'Light & Heat'Ställ in MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")'Om den inte hittas, avslutaOm MyRange är ingenting, avsluta Sub'Visa första adressen hittadMsgBox MyRange.Adress'Gör en kopia av intervallobjektetAnge OldRange = MyRange'Lägg till adressen till strängen som avgränsar med "|" karaktärFindStr = FindStr & "|" & MyRange.Adress'Iterera genom intervallet och leta efter andra instanserDo'Sök efter' Light & Heat 'med den tidigare hittade adressen som efterparameternStäll in MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Om adressen redan har hittats, avsluta do -slingan - detta stoppar kontinuerlig loopingOm InStr (FindStr, MyRange.Address) Avsluta Gör'Visa den senast hittade adressenMsgBox MyRange.Adress'Lägg till den senaste adressen till adresssträngenFindStr = FindStr & "|" & MyRange.Adress'gör en kopia av det aktuella intervalletAnge OldRange = MyRangeSlingaAvsluta Sub |
Denna kod kommer att iterera genom det använda intervallet och visa adressen varje gång den hittar en instans av 'Light & Heat'
Observera att koden fortsätter att loopa tills en duplicerad adress hittas i FindStr, i så fall kommer den att lämna Do -slingan.
LookIn -parameter
Du kan använda LookIn -parameter för att ange vilken komponent i cellen du vill söka i. Du kan ange värden, formler eller kommentarer i en cell.
- xlVärden - Söker cellvärden (cellens slutvärde efter beräkning)
- xlFormler - Sökningar i själva cellformeln (vad som än anges i cellen)
- xlKommentarer - Söker inom cellanteckningar
- xlCommentsThreaded - Söker inom cellkommentarer
Om vi antar att en formel har angetts i kalkylbladet kan du använda den här exempelkoden för att hitta den första platsen för vilken formel som helst:
| 12345678910 | Sub TestLookIn ()Dim MyRange As RangeStäll in MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Om inte MyRange är ingenting dåMsgBox MyRange.AdressAnnanMsgBox "Hittades inte"Avsluta omAvsluta Sub |
Om "LookIn" -parametern var inställd på xlValues, skulle koden visa ett "Not Found" -meddelande. I det här exemplet returnerar det B10.
Använda LookAt -parametern
De LookAt -parameter bestämmer om sökningen kommer att söka efter en exakt cellmatchning eller om den ska söka efter någon cell som innehåller sökvärdet.
- xlHela - Kräver hela cellen för att matcha sökvärdet
- xlDel - Sökningar i en cell efter söksträngen
Detta kodexempel hittar den första cellen som innehåller texten "ljus". Med Lookat: = xlPart, det kommer att returnera en match för “Light & Heat”.
| 123456789 | Sub TestLookAt ()Dim MyRange As RangeStäll in MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Om inte MyRange är ingenting dåMsgBox MyRange.AdressAnnanMsgBox "Hittades inte"Avsluta omAvsluta Sub |
Om xlHela var inställd, skulle en matchning bara återvända om cellvärdet var "lätt".
SearchOrder -parameter
De SearchOrder -parameter dikterar hur sökningen kommer att utföras i hela intervallet.
- xlRader - Sökningen görs rad för rad
- xlXolumns - Sökningen görs kolumn för kolumn
| 123456789 | Sub TestSearchOrder ()Dim MyRange As RangeAnge MyRange = Sheets ("Sheet1"). UsedRange.Find ("anställd", SearchOrder: = xlColumns)Om inte MyRange är ingenting dåMsgBox MyRange.AdressAnnanMsgBox "Hittades inte"Avsluta omAvsluta Sub |
Detta påverkar vilken matchning som kommer att hittas först.
Med hjälp av testdata som angavs i kalkylbladet tidigare, när sökordern är kolumner, är den lokaliserade cellen A5. När parametern för sökordern ändras till xlRows är den lokaliserade cellen C4
Detta är viktigt om du har dubblettvärden inom sökområdet och du vill hitta den första instansen under ett visst kolumnnamn.
SearchDirection -parameter
De SearchDirection -parameter dikterar åt vilket håll sökningen kommer att gå - effektivt framåt eller bakåt.
- xlNästa - Sök efter nästa matchande värde i intervallet
- xlFöregående - Sök efter tidigare matchningsvärde i intervallet
Återigen, om det finns dubblettvärden inom sökområdet kan det ha en effekt på vilket som hittas först.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange As RangeStäll in MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Om inte MyRange är ingenting dåMsgBox MyRange.AdressAnnanMsgBox "Hittades inte"Avsluta omAvsluta Sub |
Med denna kod på testdata returnerar en sökriktning för xlPrevious en plats på C9. Genom att använda xlNext -parametern returneras en plats på A4.
Nästa parameter betyder att sökningen börjar i det övre vänstra hörnet av sökområdet och fungerar nedåt. Den föregående parametern innebär att sökningen startar i det nedre högra hörnet av sökområdet och fungerar uppåt.
MatchByte -parameter
De MatchBye -parameter används bara för språk som använder en dubbel byte för att representera varje tecken, till exempel kinesiska, ryska och japanska.
Om den här parametern är inställd på "True" matchar Find bara dubbelbyte-tecken med dubbelbyte-tecken. Om parametern är inställd på 'False', kommer ett dubbelbyte-tecken att matcha med enstaka eller dubbelbyte-tecken.
SearchFormat -parameter
De SearchFormat -parameter gör att du kan söka efter matchande cellformat. Detta kan vara ett särskilt teckensnitt som används, eller ett fetstil, eller en textfärg. Innan du använder den här parametern måste du ange det format som krävs för sökningen med egenskapen Application.FindFormat.
Här är ett exempel på hur du använder det:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueStäll in MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Om inte MyRange är ingenting dåMsgBox MyRange.AdressAnnanMsgBox "Hittades inte"Avsluta omApplication.FindFormat.ClearAvsluta Sub |
I det här exemplet, FindFormat egenskapen är inställd på att leta efter ett fetstil. Sökordet Sök söker sedan efter ordet "värme" och ställer in SearchFormat -parametern till True så att den bara returnerar en förekomst av texten om teckensnittet är fet.
I data som tidigare visats i kalkylbladet returnerar detta A9, som är den enda cellen som innehåller ordet 'värme' med fetstil.
Se till att egenskapen FindFormat rensas i slutet av koden. Om du inte gör det kommer din nästa sökning fortfarande att ta hänsyn till detta och returnera felaktiga resultat.
Där du använder en SearchFormat -parameter kan du också använda jokertecken (*) som sökvärde. I det här fallet kommer det att söka efter valfritt värde med en fetstil:
| 1 | Ställ in MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Använda flera parametrar
Alla sökparametrar som diskuteras här kan användas i kombination med varandra om det behövs.
Du kan till exempel kombinera "LookIn" -parametern med "MatchCase" -parametern så att du tittar på hela celltexten, men den är skiftlägeskänslig
| 123456789 | Sub TestMultipleParameters ()Dim MyRange As RangeStäll in MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Om inte MyRange är ingenting dåMsgBox MyRange.AdressAnnanMsgBox "Hittades inte"Avsluta omAvsluta Sub |
I det här exemplet kommer koden att returnera A4, men om vi bara använde en del av texten t.ex. "Värme", skulle ingenting hittas eftersom vi matchar hela cellvärdet. Det skulle också misslyckas på grund av att fallet inte matchade.
| 1 | Ställ in MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Ersätt i Excel VBA
Det finns, som du kan förvänta dig, en ersättningsfunktion i Excel VBA, som fungerar på ett mycket liknande sätt som "Sök" men ersätter värdena på cellplatsen med ett nytt värde.
Det här är parametrarna som du kan använda i ett Ersätt metoduttalande. Dessa fungerar på exakt samma sätt som för Find method statement. Den enda skillnaden mot "Sök" är att du måste ange en ersättningsparameter.
| namn | Typ | Beskrivning | Värden |
| Vad | Nödvändig | Värdet att söka efter | Vilken datatyp som helst som en sträng eller numerisk |
| Ersättning | Nödvändig | Ersättningssträngen. | Vilken datatyp som helst som en sträng eller numerisk |
| Titta på | Frivillig | Matcha hela eller hela cellen | xlPart eller xlWhole |
| SearchOrder | Frivillig | Ordningen att söka i - Rader eller kolumner | xlByRows eller xlByColumns |
| Liknande fall | Frivillig | Sökning är skiftlägeskänslig eller inte | Sant eller falskt |
| MatchByte | Frivillig | Används endast om du har installerat språkstöd för dubbel byte | Sant eller falskt |
| SearchFormat | Frivillig | Tillåt sökning efter cellformat | Sant eller falskt |
| ReplaceFormat | Frivillig | Ersättningsformatet för metoden. | Sant eller falskt |
Parametern Ersätt format söker efter en cell med ett visst format t.ex. fet på samma sätt som parametern SearchFormat fungerar i metoden Sök. Du måste ställa in egenskapen Application.FindFormat först, som visas i koden Hitta exempel som visas tidigare
Ersätt utan valfria parametrar
På det enklaste behöver du bara ange vad du söker efter och vad du vill ersätta det med.
| 123 | Sub TestReplace ()Sheets ("Sheet1"). UsedRange.Replace What: = "Light & Heat", Ersättning: = "L & H"Avsluta Sub |
Observera att sökmetoden endast returnerar den första förekomsten av det matchade värdet, medan ersättningsmetoden fungerar genom hela det angivna intervallet och ersätter allt som den hittar en matchning på.
Ersättningskoden som visas här kommer att ersätta varje instans av 'Light & Heat' med 'L & H' genom hela cellområdet som definieras av UsedRange -objektet
Använda VBA för att hitta eller ersätta text i en VBA -textsträng
Ovanstående exempel fungerar utmärkt när du använder VBA för att interagera med Excel -data. För att interagera med VBA-strängar kan du dock använda inbyggda VBA-funktioner som INSTR och REPLACE.
Du kan använda INSTR -funktion för att hitta en textsträng inom en längre sträng.
| 123 | Sub TestInstr ()MsgBox InStr ("This is MyText string", "MyText")Avsluta Sub |
Denna exempelkod returnerar värdet 9, vilket är nummerpositionen där "MyText" finns i strängen som ska sökas.
Observera att det är skiftlägeskänsligt. Om "MyText" är små bokstäver returneras ett värde på 0 vilket innebär att söksträngen inte hittades. Nedan kommer vi att diskutera hur du inaktiverar skiftlägeskänslighet.
INSTR - Start
Det finns ytterligare två valfria parametrar tillgängliga. Du kan ange startpunkten för sökningen:
| 1 | MsgBox InStr (9, "This is MyText string", "MyText") |
Startpunkten anges som 9 så den kommer fortfarande att returnera 9. Om startpunkten var 10, skulle den returnera 0 (ingen matchning) eftersom startpunkten skulle vara för långt framåt.
INSTR - Case Sensitivity
Du kan också ställa in en Jämför parameter till vbBinaryCompare eller vbTextCompare. Om du anger denna parameter måste satsen ha ett startparametervärde.
- vbBinaryCompare - skiftlägeskänslig (standard)
- vbTextCompare - Inte skiftlägeskänslig
| 1 | MsgBox InStr (1, "This is MyText string", "mytext", vbTextCompare) |
Detta uttalande kommer fortfarande att returnera 9, även om söktexten är med små bokstäver.
För att inaktivera skiftlägeskänslighet kan du också deklarera Alternativjämför text högst upp i kodmodulen.
VBA -ersättningsfunktion
Om du vill ersätta tecken i en sträng med annan text i din kod, är ersättningsmetoden idealisk för detta:
| 123 | Sub TestReplace ()MsgBox Replace ("This is MyText string", "MyText", "My Text")Avsluta Sub |
Denna kod ersätter "MyText" med "Min text". Observera att söksträngen är skiftlägeskänslig eftersom en binär jämförelse är standard.
Du kan också lägga till andra valfria parametrar:
- Start - definierar position i den initiala strängen som ersättaren måste utgå från. Till skillnad från i Find -metoden returnerar den en stympad sträng med utgångspunkt från teckennumret som definieras av parametern Start.
- Räkna - definierar antalet byten som ska göras. Som standard ändrar Ersätt varje förekomst av söktexten som hittas, men du kan begränsa detta till en enda ersättare genom att ställa in parametern Count till 1
- Jämföra - som i metoden Sök kan du ange en binär sökning eller en textsökning med vbBinaryCompare eller vbTextCompare. Binärt är skiftlägeskänsligt och text är inte skiftlägeskänsligt
| 1 | MsgBox Replace ("This is MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Denna kod returnerar "Min textsträng (mytext)". Detta beror på att den angivna startpunkten är 9, så den nya returnerade strängen börjar med tecken 9. Endast den första "MyText" har ändrats eftersom Count -parametern är inställd på 1.
Ersättmetoden är idealisk för att lösa problem som folks namn som innehåller apostrofer t.ex. O'Flynn. Om du använder enkla citattecken för att definiera ett strängvärde och det finns en apostrof, kommer detta att orsaka ett fel eftersom koden kommer att tolka apostrofen som strängens slut och inte kommer att känna igen resten av strängen.
Du kan använda ersättningsmetoden för att ersätta apostrofen med ingenting och ta bort den helt.
