Find hledej - od A do Z - Excel VBA

Úvodem do hledání (Find)

Potřebujete-li vyhledávat pomocí VBA. Od teorie přes praktické příklady až po tipy a triky. Pro přehlednost je článek rozdělen na jednotlivé kapitoly:

• Teorie pro metodu Find Range.Find
• Základní použití Find Range.Find
• Najdi a označ hodnotu - využitím Selection
• Upřesněné možností hledání:
  • Najdi "jinde" než v buňce - ve vzorci, komentáři, poznámce, vlákně
  • Typ hledání - přesná, přibližná
  • Pořadí hledání - řádky, sloupce
  • Hledání od
  • Směr hledání - předchozí, následující
  • Velká a malá písmena - předchozí, následující
  • Hledání s využitím formátu - předchozí, následující
• Exituje hledaná hodnota v oblasti? - pokud ne/existuje info
• Na kterém řádku se naléza hledaná hodnota
• Adresa buňky ve které je hledaná hodnota
• Zástupné znaky *, ?, ~ (tilda)
• Poslední radek sloupec
• další příklady v přípravě
• Sešit ke stažení - zdarma
• Související články

Teorie Find

Trocha teorie ohledně Find.

Syntaxe

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Jméno	Poviné/Volitelné	Popis
What	Poviné	Hodnota kterou hledáte
After	Volitelné	Buňka od které chcete začít hledat. Začne se až po této buňce ( ve specifikované buňce není hledáno - od této ). Pokud není určeno hledání začne v levém horním rohu zadaného rozsahu.
LookIn	Volitelné	( xlComments, xlFormulas, xlValues, xlComments, xlCommentsThreaded )
LookAt	Volitelné	( xlWhole, xlPart )
SearchOrder	Volitelné	( xlByRows , xlByColumns )
SearchDirection	Volitelné	( xlNext, xlPrevious )
MatchCase	Volitelné	( True , False – defaultně nastaveno).
MatchByte	Volitelné
SearchFormat	Volitelné

Základní použití metody Find

Nejednodužší je říct v jaké oblasti a co hledám:

Range("A1:A5").Find("Jena")

Jenže pokud se (ne)najde tak nevím kde. Mohu využít, že výsledek naSETují a pak si zobrazím adresu buňky:

' zjistím a hzobrazím adresu buňky ve které se naléza Set OblastNajdi = Range("A1:A5").Find("Ivo") MsgBox (OblastNajdi.Address) ' Pokuid nechci využít MsgBox tak mohu vypsat do DebugPrint Debug.Print OblastNajdi.Address

Nebo buňku vyberu využitím Select

' první nalezenou buňku označím Set OblastNajdi = Range("A1:A5").Find("Ivo") OblastNajdi.Select

Problémem bude když nenajdu (skončí chybovou hláškou), mohu ošetřit:

Set OblastNajdi = Range("A1:A5").Find("Ivo") If OblastNajdi Is Nothing Then MsgBox("Nenalezeno") Else MsgBox (OblastNajdi.Address) Debug.Print OblastNajdi.Address End If

Mohu zobrazovat i další informace, řádek, sloupec:

MsgBox ("Radek: " & OblastNajdi.Row) MsgBox ("Adresa: " & OblastNajdi.Address) MsgBox ("Sloupec: " & OblastNajdi.Column)

Označ hodnotu využitím Selection

Jak najít v označené (vymezené) oblasti, například hodnotu 4 a označit ji. Najde tu první.

Dim rng As Range Range("A1:C70").Select '' klidně nemusím využít What a přiřazení Set rng = Selection.Find(What:="4") ''Set rng = Selection.Find("4") If Not rng Is Nothing Then rng.Select End If

Označ buňky se vzorcem. Jak najít a označit buňky, které obsahují vzorec. Využítím For Each (pro všechny):

For Each cl In ActiveSheet.UsedRange If cl.HasFormula() = True Then cl.Interior.ColorIndex = 21 End If Next cl

Najdi jinde - ve vzorci, komentáři, poznámce

Nemusíme prohledávat jen hodnoty v buňce, někdy se nám hodí prohledávat i vzorce (zda se ve vzorci nenachází hodnota), případně v poznámce, komentáři:

Prohledávaní vzorců, funkcí

Prohledává vzorce, hledam li hodnotu 2, tak v buňce mohu vuiděl výsledek funkce =2+2, a při použití LookIn mi toto buňku najde ve vzorci je dvojka:

Set OblastNajdi = Range("A1:A20").Find("2", LookIn:=xlFormulas) OblastNajdi.Select Debug.Print OblastNajdi.Address ' najde ve vzorci

Prohledává hodnoty

Klasika na kteoru jste zvyklí

' najde v hodnotách Set OblastNajdi = Range("A1:A20").Find("22", LookIn:=xlValues) OblastNajdi.Select Debug.Print OblastNajdi.Address

Poznámky

Prohledává ony žluté štítky, poznámky

' najde v poznámkách Set OblastNajdi = Range("A1:A20").Find("22", LookIn:=xlNotes) OblastNajdi.Select Debug.Print OblastNajdi.Address

Prohledávat komentáře

Office 365 mají kromě poznámek komentáře, toto nastavení má prohledávat komentáře, při mém testování opět prohledávalo poznámky:

' najde v poznámkách, nehledá v komentářích (Office 365) Set OblastNajdi = Range("A1:A20").Find("33", LookIn:=xlComments) OblastNajdi.Select Debug.Print OblastNajdi.Address

Prohledává vlákna komentářů

Hledá text ve vláknéch komentáře, nehledá text v prvním komentáři, což by měl dělat xlComments:

' najde ve vlákně komentářů - nová verze Excel 356 Set OblastNajdi = Range("A1:A20").Find("33", LookIn:=xlCommentsThreaded) OblastNajdi.Select Debug.Print OblastNajdi.Address

Neřeším případ, že není nalezeno, což už z předchizí ukázky umíme ošetřit.

Typ hledání

Mameli v oblasti slova "Test 2", "Test", pak přesné hledání slova Test najde opravdu jen buňku se slovem Test, přibližná shoda najde i slova obsahujíci Test tedy i Test 2

Přesné znění

' najde v hodnotách přesně znění Set OblastNajdi = Range("A1:A20").Find("Test", LookAt:=xlWhole) OblastNajdi.Select Debug.Print OblastNajdi.Address

Obsahující dané slovo "přibližná"

' najde v hodnotách které dané slovo v textu obsahují - přibližná shoda Set OblastNajdi = Range("A1:A20").Find("Test", LookAt:=xlPart) OblastNajdi.Select Debug.Print OblastNajdi.Address

Pořadí hledání - řádky sloupce

Někde je potřeba prohledávat oblast po řádcích někdy po sloupcích:

Hledání po řádcích

' hledej po řádcích Set OblastNajdi = Range("A83:C85").Find(What:="Test", SearchOrder:=xlByRows) MsgBox ("Radek: " & OblastNajdi.Address)

Hledání po sloupcích

' hledej po sloupcích Set OblastNajdi = Range("A83:C85").Find(What:="Test", SearchOrder:=xlByColumns) MsgBox ("Radek: " & OblastNajdi.Address)

Hledání od buňky

Při práci v Excel je vhodné sdělit od které buňky se začne prohledávat. V praxi většinou v první buňce je jméno sloupce, který se neprohledává. Budeme-li brát v uvatu hledání v celém sloupci od první buňky ve které bude hledaná hodnota, najde se nejprve další v pořadí:

Hledaní bez určení od které buňky

Set OblastNajdi = Range("A:A").Find("Eva") MsgBox (OblastNajdi.Address) ' Najde A4

Od které buňky hledá

Začneme od poslední, neboli první prohledávaná buňka bude A1

Set OblastNajdi = Range("A:A").Find("Eva", _ After:=Range("A:A").Cells(Range("A:A").Cells.Count)) MsgBox (OblastNajdi.Address) ' Najde A1

Směr hledání - následující, předchozí

Někde je potřeba hledat ve směru následujíci, někdy předchozí. Počítá se od uvedené buňky (neboli od buďky dolu nahoru. Případně můžeme použí metodu FindNext.

Předchozí

Set OblastNalezeno = Range("A1:A9").Find("Eva" _ , After:=Range("A5"), SearchDirection:=xlPrevious) MsgBox ("Předchozí: " & OblastNalezeno.Address)

Následující

Set OblastNalezeno = Range("A1:A9").Find("Eva" _ , After:=Range("A5"), SearchDirection:=xlNext) MsgBox ("Další: " & OblastNalezeno.Address)

Metoda FindNext

bude popsáno v samostatném článku, ale pro doplnění, nebo ať nemusíte hledat. Prochází postupně buňky. Samozdřejmě je vhodné doplnit a začít hledat jakoby od poslední buňky, neboli ve skutečnosti se bude hledat od buňky A1, ne jako v následujicí ukázce A2.

' zjistím adresu buňky Dim Oblast As Range Set Oblast = Range("A:A") Set OblastNalezeno = Oblast.Find("Eva") MsgBox ("První: " & OblastNalezeno.Address) PrvniNalez = OblastNalezeno.Address Do Set OblastNalezeno = Oblast.FindNext(OblastNalezeno) ' pozor na zacyklení MsgBox (OblastNalezeno.Address) Loop While PrvniNalez <> OblastNalezeno.Address

Velká a malá písmena

Pokud chcete hledat a bude vám záležet i na velikosti písmen bude potřeba nastavit MatchCase:=True:

Pokud nezáleží na velikosti

Můžete nastavit MatchCase:=False

Set OblastNalezeno = Range("A115:A120").Find("Eva", MatchCase:=False) MsgBox ("Nalezeno: " & OblastNalezeno.Address) ' Najde A118

Jelikož metoda find hledá bez ohledu na velikost písmen můžeme tento parametr vynechat:

Set OblastNalezeno = Range("A115:A120").Find("Eva") MsgBox ("Nalezeno: " & OblastNalezeno.Address) ' Najde A118

Pokud záleží na velikosti

Musíme nastavit MatchCase:=Ture až poté se respektují velká a malá písmena:

Set OblastNalezeno = Range("A115:A120").Find("Eva", MatchCase:=True) MsgBox ("Nalezeno: " & OblastNalezeno.Address) ' Najde A120

Hledání s formátem

Pokud chcete hledat zaroveň podle formátu:

Bez formátu

Můžete nastavit SearchFormat:=False, případně nemusíme uvádět:

Set OblastNalezeno = Range("A135:A140").Find("Eva", SearchFormat:=False) MsgBox ("Nalezeno: " & OblastNalezeno.Address) ' Najde A138

S formátem

Musíme nastavit SearchFormat:=True a uvést požadovaný formát FindFormát. Více sepíšui v samostatném článku.

' Pro jistotu formát vymažu Application.FindFormat.Clear Application.FindFormat.Font.Bold = True Set OblastNalezeno = Range("A135:A140").Find("Eva", SearchFormat:=True) MsgBox ("Nalezeno: " & OblastNalezeno.Address) ' Najde A140 ' Pro jistotu formát vymažu Application.FindFormat.Clear

Exituje hledaná hodnota v oblasti?

Informovat za bylo nebo nebylo nalezeno. Pomocí dialogového okna. Využívá Nothing, ktreré se vrací pokud není v prohledávané oblasti nalezeno.

Dim rng As Range Set rng = Cells.Find(What:="4", LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False) If Not rng Is Nothing Then MsgBox ("Nalezeno") Else MsgBox ("Nenalezeno") End If

Na kterém řádku se naléza hledaná hodnota

Jelli potřeba zjistit na kterém řádku se hodnota naléza.

With Sheets("List2").Range("A:A") Set Rng = .Find(What:="2", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then MsgBox ("Radek: " & Rng.Row) Else MsgBox "Nenalezeno" Exit Sub End If End With

Adresa buňky ve které je hledaná hodnota

vyjdeme z předchozího kódu jen upravíme:

MsgBox ("Adresa: " & Rng.Address) ' podobně pro sloupec MsgBox ("Sloupec: " & Rng.Column)

Zástupné znaky v metodě Find

Pokud je potřeba hledat s využitím zástupných znaků:

• ? - právě jeden znak
• * - posloupnost znaků
• ~ - tilda - hledá se co je za vlnovkou neboli mohu hledat i hvězdičky ;)

Set OblastNajdi = Range("A:A").Find("Iv*") MsgBox (OblastNajdi.Address) ' Najde A4 Set OblastNajdi = Range("A:A").Find("Iv??") MsgBox (OblastNajdi.Address) ' Najde A5 Set OblastNajdi = Range("A:A").Find("*l") MsgBox (OblastNajdi.Address) ' Najde A7 Set OblastNajdi = Range("A:A").Find("*~**") MsgBox (OblastNajdi.Address) ' Najde A8

Poslední řádek a sloupec

Ukázka jak ještě využít medoru Find

Dim PosledniSloupec As Integer Dim PosledniRadek As Long If WorksheetFunction.CountA(Cells) > 0 Then PosledniRadek = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row PosledniSloupec = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column MsgBox ("Poslední obsazena buňka má řádek: " & PosledniRadek & " sloupec: " & PosledniSloupec) Else MsgBox ("Prázdný list") End If

Ke stažení

Soubor Find - Hledej - praktické příklady - Excel VBA ke stažení zdarma. Soubor využívá makra (pro Excel 2007 a novější).

Další související články:

Pokud už máte buňku (oblast) označenou můžete s ními dále pracovat například

• RESIZE, OFFSET - zvětšit posounout oblast
• Zapiš údaj do buňky (oblasti)
• Přečti údaj z buňky (oblasti)
• Formátování buněk datum a čas
• ...

Závěrem

Máte svůj oblíbený kód pro hledání, nebo používate nějakou fintu, které ještě není uvedena. Můžete se pochlubit v komentářích.

Článek byl aktualizován: 17.01.2021 14:08

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.