Ez az oktatóanyag bemutatja, hogyan kell használni a Keresés és csere módszereket az Excel VBA -ban.
VBA Find
Az Excel kiválóan beépített megtalálja és Keresés és csere eszközöket.
A parancsikonokkal aktiválhatók CTRL + F (Find) vagy CTRL + H (Csere) vagy a szalagon keresztül: Kezdőlap> Szerkesztés> Keresés és kiválasztás.
Kattintással Lehetőségek, láthatja a speciális keresési lehetőségeket:
A VBA segítségével könnyedén elérheti a Keresés és a Csere módszereket is. Ezek a beépített módszerek sokkal gyorsabbak, mint bármi, amit saját maga írhat a VBA-ban.
Keresse meg a VBA példáját
A Find funkció bemutatása érdekében a következő adatkészletet hoztuk létre az 1. lapon.
Ha szeretné követni, írja be az adatokat a saját munkafüzetébe.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA Find opcionális paraméterek nélkül
A VBA Find módszer használatakor sok választható paraméter állítható be.
Erősen javasoljuk az összes paraméter definiálását a Find Method használatakor!
Ha nem határozza meg az opcionális paramétereket, a VBA a jelenleg kiválasztott paramétereket használja az Excel Keresés ablakában. Ez azt jelenti, hogy nem tudja, milyen keresési paramétereket használnak a kód futtatásakor. A keresés futtatható a teljes munkafüzeten vagy egy lapon. Képleteket vagy értékeket kereshet. Ezt nem lehet tudni, hacsak manuálisan nem ellenőrzi, hogy mi van kiválasztva az Excel Keresés ablakában.
Az egyszerűség kedvéért egy példával kezdjük, amelyben nincsenek megadva opcionális paraméterek.
Példa egyszerű keresésre
Nézzünk egy egyszerű keresési példát:
| 123456789 | Sub TestFind ()Dim MyRange mint tartományÁllítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("alkalmazott")MsgBox MyRange.CímMsgBox MyRange.OszlopMsgBox MyRange.RowEnd Sub |
Ez a kód az alkalmazott laptartományban az „alkalmazott” kifejezésre keres. Ha „alkalmazottat” talál, akkor az első talált tartományt hozzárendeli a MyRange tartományváltozóhoz.
Ezután megjelennek az üzenetdobozok a talált szöveg címével, oszlopával és sorával.
Ebben a példában az alapértelmezett keresési beállításokat használja (feltéve, hogy nem módosították az Excel keresési ablakában):
- A keresési szöveg részben illeszkedik a cella értékéhez (pontos cellaegyezés nem szükséges)
- A keresés nem különbözteti meg a kis- és nagybetűket.
- A Find csak egyetlen munkalapon keres
Ezek a beállítások különféle opcionális paraméterekkel módosíthatók (az alábbiakban tárgyaljuk).
Keresse meg a módszer megjegyzéseit
- A Find nem választja ki azt a cellát, ahol a szöveg található. Csak a talált tartományt azonosítja, amelyet a kódjában manipulálhat.
- A Find módszer csak az első talált példányt fogja megkeresni.
- Használhat helyettesítő karaktereket (*) pl. „E*” keresése
Nincs találat
Ha a keresési szöveg nem létezik, akkor a tartományobjektum üres marad. Ez komoly problémát okoz, amikor a kód megpróbálja megjeleníteni a helyértékeket, mert azok nem léteznek. Ennek eredményeként olyan hibaüzenet jelenik meg, amelyet nem szeretne.
Szerencsére az Is Operator segítségével tesztelhet egy üres tartományú objektumot a VBA -n belül:
| 1 | Ha nem a MyRange az semmi |
A kód hozzáadása az előző példánkhoz:
| 12345678910111213 | Sub TestFind ()Dim MyRange mint tartományÁllítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("alkalmazott")Ha nem a MyRange az semmiMsgBox MyRange.CímMsgBox MyRange.OszlopMsgBox MyRange.RowMásMsgBox "Nem található"Vége HaEnd Sub |
Keresse meg a paramétereket
Eddig csak a Find módszer használatának alapvető példáját néztük meg. Azonban számos opcionális paraméter áll rendelkezésre a keresés finomításához
| Paraméter | típus | Leírás | Értékek |
| Mit | Kívánt | A keresendő érték | Bármilyen adattípus, például karakterlánc vagy numerikus |
| Után | Választható | Egycellás hivatkozás a keresés megkezdéséhez | Cella címe |
| Benéz | Választható | A kereséshez használjon képleteket, értékeket, megjegyzéseket | xlValues, xlFormulas, xlComments |
| Nézd meg | Választható | Egyezik a cella egy része vagy egésze | xlEgész, xlRész |
| SearchOrder | Választható | A sorrend vagy oszlop keresési sorrendje | xlByRows, xlByColummns |
| SearchDirection | Választható | A keresés iránya - előre vagy hátra | xlKövetkező, xl |
| Mérkőzés esetén | Választható | A keresés megkülönbözteti a kis- és nagybetűket, vagy sem | Igaz vagy hamis |
| MatchByte | Választható | Csak akkor használható, ha dupla bájtos nyelvi támogatást telepített, pl. Kínai nyelv | Igaz vagy hamis |
| SearchFormat | Választható | A cella formátuma szerinti keresés engedélyezése | Igaz vagy hamis |
Paraméter és több érték keresése után
Ön használja a Paraméter után a keresés kezdő cellájának megadásához. Ez akkor hasznos, ha a keresett értéknek egynél több példánya van.
Ha a keresés már talált egy értéket, és tudja, hogy több értéket talál, akkor használja a Find módot az „After” paraméterrel az első példány rögzítéséhez, majd használja ezt a cellát a következő keresés kiindulópontjaként.
Ezt használhatja a keresési szöveg több példányának megkeresésére:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Dim MyRange mint tartomány, OldRange mint tartomány, FindStr karakterláncként"Keresse meg a" Light & Heat "első példányátÁllítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")- Ha nem találja, lépjen kiHa a MyRange semmi, akkor lépjen ki a Sub -ból'Az első cím megtalálásaMsgBox MyRange.CímKészítsen másolatot a tartományobjektumrólOldRange = MyRange beállítása'Adja hozzá a címet a "|" karakterlánccal határoló karakterlánchoz karakterFindStr = FindStr & "|" & MyRange.Cím- Ismételje meg a tartományt, és keressen más példákatTedd„Keresse meg a„ Light & Heat ”kifejezést az előzőleg talált címet használva After paraméterkéntÁllítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Ha már megtalálta a címet, akkor lépjen ki a do ciklusból - ez leállítja a folyamatos hurkolástHa InStr (FindStr, MyRange.Address), akkor lépjen ki'Jelenítse meg a legutóbb talált címetMsgBox MyRange.Cím'Adja hozzá a legújabb címet a címsorhozFindStr = FindStr & "|" & MyRange.Cím'készítsen másolatot az aktuális tartományrólOldRange = MyRange beállításaHurokEnd Sub |
Ez a kód végigmegy a használt tartományon, és minden alkalommal megjeleníti a címet, amikor „Light & Heat” példányt talál
Ne feledje, hogy a kód mindaddig ciklusban marad, amíg a FindStr nem talál egy ismétlődő címet, ebben az esetben kilép a Do ciklusból.
LookIn Parameter
Használhatja a LookIn paraméter megadhatja, hogy a cella melyik összetevőjében szeretne keresni. Megadhat értékeket, képleteket vagy megjegyzéseket egy cellában.
- xlValues - Cellaértékeket keres (a cella végső értéke a számítás után)
- xlKéplet - Magában a cellaképletben keres (bármit megad a cellába)
- xlMegjegyzések - Keresés a cellajegyzetekben
- xlCommentsThreaded - Keresés a cella megjegyzéseiben
Feltételezve, hogy egy képletet írtak be a munkalapra, ezt a példakódot használva megtalálhatja bármely képlet első helyét:
| 12345678910 | Sub TestLookIn ()Dim MyRange mint tartományÁllítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Ha nem a MyRange az semmiMsgBox MyRange.CímMásMsgBox "Nem található"Vége HaEnd Sub |
Ha a „LookIn” paraméter xlValues értékre van állítva, a kód a „Not Found” üzenetet jeleníti meg. Ebben a példában a B10 értéket adja vissza.
A LookAt paraméter használata
Az LookAt paraméter meghatározza, hogy a keresés pontos cellaegyeztetést keres, vagy a keresési értéket tartalmazó cellákat keresi.
- xlEgész - A teljes cellának meg kell egyeznie a keresési értékkel
- xlRész - A cellában keresi a keresési karakterláncot
Ez a kódpélda megkeresi a „light” szöveget tartalmazó első cellát. Val vel Név: = xlPart, visszaadja a „Light & Heat” meccset.
| 123456789 | Sub TestLookAt ()Dim MyRange mint tartományÁllítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Ha nem a MyRange az semmiMsgBox MyRange.CímMásMsgBox "Nem található"Vége HaEnd Sub |
Ha xlEgész beállítva, az egyezés csak akkor tér vissza, ha a cella értéke „világos”.
SearchOrder paraméter
Az SearchOrder paraméter diktálja, hogy a keresést hogyan hajtják végre az egész tartományban.
- xlRows - A keresés soronként történik
- xlXoszlopok - A keresés oszloponként történik
| 123456789 | Sub TestSearchOrder ()Dim MyRange mint tartományÁllítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("alkalmazott", SearchOrder: = xlColumns)Ha nem a MyRange az semmiMsgBox MyRange.CímMásMsgBox "Nem található"Vége HaEnd Sub |
Ez befolyásolja azt, hogy melyik egyezést találja meg először.
A korábban a munkalapba bevitt tesztadatok felhasználásával, amikor a keresési sor oszlopok, a található cella A5. Ha a keresési sorrend paraméterét xlRows -ra változtatja, a hely a C4
Ez akkor fontos, ha a keresési tartományon belül ismétlődő értékek vannak, és az első példányt egy adott oszlopnév alatt szeretné megtalálni.
SearchDirection paraméter
Az SearchDirection paraméter diktálja, hogy a keresés melyik irányba halad - hatékonyan előre vagy hátra.
- xlKövetkező - Keresse meg a következő egyező értéket a tartományban
- xlElőző - Keresse meg az előző egyező értéket a tartományban
Ismételten, ha a keresési tartományon belül ismétlődő értékek találhatók, az hatással lehet arra, hogy melyik található először.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange mint tartományÁllítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Ha nem a MyRange az semmiMsgBox MyRange.CímMásMsgBox "Nem található"Vége HaEnd Sub |
Ezt a kódot használva a tesztadatokon az xlPrevious keresési iránya C9 helyet ad vissza. Az xlNext paraméter használata A4 -es helyet ad vissza.
A Next paraméter azt jelenti, hogy a keresés a keresési tartomány bal felső sarkában kezdődik, és lefelé halad. Az előző paraméter azt jelenti, hogy a keresés a keresési tartomány jobb alsó sarkában kezdődik, és felfelé halad.
MatchByte paraméter
Az MatchBye paraméter csak azoknál a nyelveknél használatos, amelyek kettős bájtot használnak az egyes karakterek ábrázolására, például kínai, orosz és japán.
Ha ez a paraméter „True” értékre van állítva, akkor a Find csak a kétbájtos karaktereket és a kétbájtos karaktereket fogja találni. Ha a paraméter értéke „False”, akkor a dupla bájtos karakter illeszkedik az egy- vagy kétbájtos karakterekhez.
SearchFormat Paraméter
Az SearchFormat paraméter lehetővé teszi a megfelelő cellaformátumok keresését. Ez lehet egy adott betűtípus, egy vastag betű vagy egy szöveg színe. A paraméter használata előtt be kell állítania a kereséshez szükséges formátumot az Application.FindFormat tulajdonság használatával.
Íme egy példa a használatára:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange mint tartományApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = IgazÁllítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Ha nem a MyRange az semmiMsgBox MyRange.CímMásMsgBox "Nem található"Vége HaApplication.FindFormat.ClearEnd Sub |
Ebben a példában a FindFormat tulajdonság félkövér betűtípust keres. A Find utasítás ezután megkeresi a „heat” szót, és a SearchFormat paramétert True értékre állítja, így csak akkor adja vissza a szöveg egy példányát, ha a betűtípus félkövér.
A korábban bemutatott minta munkalap adataiban ez az A9 értéket adja vissza, amely az egyetlen cella, amely vastag betűvel tartalmazza a „Heat” szót.
Győződjön meg arról, hogy a FindFormat tulajdonság törlődik a kód végén. Ha nem teszi meg, a következő keresés ezt is figyelembe veszi, és hibás találatokat ad vissza.
Ha SearchFormat paramétert használ, akkor helyettesítő karaktert (*) is használhat keresési értékként. Ebben az esetben minden értéket félkövér betűtípussal keres:
| 1 | Állítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Több paraméter használata
Az összes itt tárgyalt keresési paraméter szükség esetén egymással kombinálva is használható.
Például kombinálhatja a „LookIn” paramétert a „MatchCase” paraméterrel, hogy a cella teljes szövegét nézze, de a kis- és nagybetűket megkülönbözteti
| 123456789 | Sub TestMultipleParameters ()Dim MyRange mint tartományÁllítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Ha nem a MyRange az semmiMsgBox MyRange.CímMásMsgBox "Nem található"Vége HaEnd Sub |
Ebben a példában a kód A4 -et ad vissza, de ha csak a szöveg egy részét használtuk pl. „Hő”, semmit sem találnánk, mert a cellaérték egészében megegyezünk. Ezenkívül meghiúsulna, mert a tok nem egyezik.
| 1 | Állítsa be a MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Csere Excel VBA -ban
Az Excel VBA -ban van egy csere funkció, amely a „Find” funkcióhoz hasonlóan működik, de a talált cella helyén lévő értékeket új értékkel helyettesíti.
Ezek a paraméterek használhatók a Replace method utasításban. Ezek pontosan ugyanúgy működnek, mint a Find metódus utasítás. Az egyetlen különbség a „Find” között az, hogy meg kell adnia egy Replacement paramétert.
| Név | típus | Leírás | Értékek |
| Mit | Kívánt | A keresendő érték | Bármilyen adattípus, például karakterlánc vagy numerikus |
| Csere | Kívánt | A helyettesítő karakterlánc. | Bármilyen adattípus, például karakterlánc vagy numerikus |
| Nézd meg | Választható | Párosítsa a cella egy részét vagy egészét | xlPart vagy xlWhole |
| SearchOrder | Választható | A keresési sorrend - Sorok vagy oszlopok | xlByRows vagy xlByColumns |
| Mérkőzés esetén | Választható | A keresés megkülönbözteti a kis- és nagybetűket, vagy sem | Igaz vagy hamis |
| MatchByte | Választható | Csak akkor használható, ha dupla bájtos nyelvi támogatást telepített | Igaz vagy hamis |
| SearchFormat | Választható | A cella formátuma szerinti keresés engedélyezése | Igaz vagy hamis |
| ReplaceFormat | Választható | A módszer helyettesítési formátuma. | Igaz vagy hamis |
A Replace Format paraméter egy adott formátumú cellát keres, pl. félkövér, ugyanúgy, mint a SearchFormat paraméter a Find metódusban. Először be kell állítania az Application.FindFormat tulajdonságot, amint azt a korábban látható példakód megkeresése mutatja
Csere opcionális paraméterek nélkül
A legegyszerűbb esetben csak meg kell adnia, hogy mit keres, és mivel kívánja kicserélni.
| 123 | Sub TestReplace ()Táblázatok ("Sheet1"). UsedRange.Replace What: = "Light & Heat", Replacement: = "L & H"End Sub |
Ne feledje, hogy a Find metódus csak az egyező érték első példányát adja vissza, míg a Csere módszer a megadott teljes tartományon keresztül működik, és lecserél mindent, amiben egyezést talál.
Az itt látható helyettesítő kód a „Light & Heat” minden példányát „L & H” -ra cseréli a UsedRange objektum által meghatározott cellák teljes tartományában
A VBA használata szöveg kereséséhez vagy cseréjéhez a VBA szöveges karakterláncban
A fenti példák nagyszerűen működnek, ha a VBA -t használja az Excel -adatokkal való interakcióhoz. A VBA karakterláncokkal való interakcióhoz azonban használhat beépített VBA funkciókat, például az INSTR és a REPLACE.
Használhatja a INSTR függvény hogy egy szövegsorozatot egy hosszabb karakterláncon belül keressen.
| 123 | Sub TestInstr ()MsgBox InStr ("Ez a MyText karakterlánc", "MyText")End Sub |
Ez a példakód visszaadja a 9 értéket, amely az a számpozíció, ahol a „MyText” megtalálható a keresendő karakterláncban.
Vegye figyelembe, hogy a kis- és nagybetűket megkülönbözteti. Ha a „MyText” csupa kisbetű, akkor a 0 érték kerül visszaadásra, ami azt jelenti, hogy a keresési karakterlánc nem található. Az alábbiakban arról beszélünk, hogyan lehet letiltani a kis- és nagybetűk megkülönböztetését.
INSTR - Kezdje
További két opcionális paraméter áll rendelkezésre. Megadhatja a keresés kezdőpontját:
| 1 | MsgBox InStr (9, "Ez a MyText karakterlánc", "MyText") |
A kezdőpont 9, így továbbra is 9. Ha a kezdőpont 10 volt, akkor 0 értéket adna vissza (nincs egyezés), mivel a kezdőpont túl messze lenne.
INSTR - Kis- és nagybetűk érzékenysége
Az Összehasonlítás paramétert a következőre is beállíthatja vbBinaryCompare vagy vbTextCompare. Ha beállítja ezt a paramétert, akkor az utasításnak rendelkeznie kell indítási paraméter értékkel.
- vbBinaryCompare - Kis- és nagybetűk megkülönböztetése (alapértelmezett)
- vbTextCompare - Nem érzékeny a kis- és nagybetűkre
| 1 | MsgBox InStr (1, "Ez a MyText karakterlánc", "mytext", vbTextCompare) |
Ez az utasítás továbbra is 9 -et ad vissza, annak ellenére, hogy a keresési szöveg kisbetűvel van írva.
A kis- és nagybetűk megkülönböztetésének letiltásához a kódmodul tetején is deklarálhatja az Option Compare Text lehetőséget.
VBA csere funkció
Ha le szeretné cserélni a karakterlánc karaktereit más szöveggel a kódon belül, akkor a Csere módszer ideális erre:
| 123 | Sub TestReplace ()MsgBox Replace ("Ez a MyText string", "MyText", "My Text")End Sub |
Ez a kód helyettesíti a „MyText” szöveget a „My Text” szöveggel. Ne feledje, hogy a keresési karakterlánc megkülönbözteti a kis- és nagybetűket, mivel a bináris összehasonlítás az alapértelmezett.
Más opcionális paramétereket is hozzáadhat:
- Rajt - meghatározza azt a pozíciót a kezdeti karakterláncban, amelyből a cserét el kell kezdeni. A Find metódustól eltérően a Start paraméter által meghatározott karakterszámból kiindulva csonka karakterláncot ad vissza.
- Számol - meghatározza a cserék számát. Alapértelmezés szerint a Csere megváltoztatja a talált keresési szöveg minden példányát, de korlátozhatja ezt egyetlen cserére, ha a Count paramétert 1 -re állítja
- Hasonlítsa össze - a Find módszerhez hasonlóan megadhat egy bináris vagy szöveges keresést a segítségével vbBinaryCompare vagy vbTextCompare. A bináris kis- és nagybetűket megkülönbözteti, a szöveget pedig nem
| 1 | MsgBox Replace ("Ez a MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Ez a kód a „My Text string (mytext)” értéket adja vissza. Ennek az az oka, hogy a megadott kezdőpont 9, tehát az új visszaadott karakterlánc a 9. karaktertől kezdődik. Csak az első „MyText” változott, mert a Count paraméter értéke 1.
A Csere módszer ideális olyan problémák megoldására, mint az emberek neve, aposztrófokat tartalmazó pl. O’Flynn. Ha egyetlen idézőjelet használ a karakterlánc értékének meghatározására, és van aposztróf, akkor ez hibát okoz, mert a kód az aposztrófot a karakterlánc végének fogja értelmezni, és nem ismeri fel a karakterlánc többi részét.
A Csere módszerrel kicserélheti az aposztrófot semmire, teljesen eltávolíthatja azt.
