Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » vba-listy-bunky » find-hledej-excel-vba

Find hledej - od A do Z - Excel VBA

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak hledat v Excel pomocí Metody Find. Kompletmní návod jak na metodu Find od A do Z.

Doplněno 2.6.2016, 16.1.2021

Úvodem do hledání (Find)

Find - Excel VBA

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 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) metoda Find - Excel VBA - hledání po řádcích sloupcích

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 metoda Find - Excel VBA - od které buňky

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 metoda Find - Excel VBA - Next - Previous

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 metoda Find - Excel VBA - velká a malá písmena

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 metoda Find - Excel VBA - SearFormat

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 metoda Find - Excel VBA - Zastupné znaky

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 metoda Find - Excel VBA - radky sloupce poslední
Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor Find - Hledej - praktické příklady - Excel VBA soubor ve formátu *.xlsm 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

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

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). 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 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře


Iva

Přidáno: 24.12.15 10:02

Dobrý den, mohl by jste mi prosím poradit, jak napsat kód, když mám buňku A1 do které zapíši např. 1+1 nebo 6/6 a v msgboxu potřebuji zobrazit výsledek? Jak mám nadefinovat, že před znaménkem je číslo a za znaménkem je také číslo a podle znaménka, aby se určilo, co bude provádět za činnost? Moc Vám děkuji

Pavel Lasák

Přidáno: 02.06.16 18:32

To Iva: Událost listu Worksheet_Change, a následně přečíst hodnotu pomocí textových funkcí rozložit, zkontrolovat zda je správné matematické znaménko a následně provést výpočet a ten zobrazit v dialogovém okně. Popřemýšlet zda není vhodnější způsob jak údaje do buněk zadávat.

Franta

Přidáno: 27.06.16 20:14

Dobrý den, mohl byste mi, prosím, poradit s jednou věcí? V buňkách A1 až D10 mám data. Do sloupce G bych potřebovat vypsat pouze ta, která začínají řetězcem "HL". Ušetřilo by mi to hodně času a práce. Děkuji moc :-)

Jojo

Přidáno: 29.06.16 11:15

To Franta:For col = 1 To 4 'stĺpce A až D For rw = 1 To 10 ' riadky If Left(Cells(rw, col), 2) = "HL" Then ' rozlišuje veľké/malé písmená rwOut = rwOut + 1 Cells(rwOut, "G") = Cells(rw, col) End If Next rw Next col






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |