Jste zde: Úvodní stránka » excel » vba-funkce-vzorce » vyhledavaci-funkce-VBA-Excel

Vyhledávací funkce ve VBA Excel

Videokurzy Excel

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

Jak na Excel - naučte se efektivně využívat Excel - nejoblíbenější (Hodnocení 97%, přes 13.000 studentů, + 750 pozitivních zpětných vazeb) - Získat kurz
MaxiKurz Excel - získejte lepší práci, více peněz, staňte se nepostradatelní (přes 6 hodin videí, 160 lekcí) - Získat kurz
G tabulky... - pokud nemáte rádi Excel využijte kurz na Google-tabulky

Jak na vyhledávací funkce pomocí VBA.

Vyhledávací funkcích ve VBA

Sem tam potřebuji použít vyhledávací funkci ve VBA. Proto jsem si připravil tento seznam:

FORMULATEXT (FORMULATEXT)
HYPERTEXTOVÝ.ODKAZ (HYPERLINK)
INDEX (INDEX)
NEPŘÍMÝ.ODKAZ (INDIRECT)
ODKAZ (ADDRESS)
POČET.BLOKŮ (AREAS)
POSUN (OFFSET)
POZVYHLEDAT (MATCH)
RTD (RTD)
ŘÁDEK (ROW)
ŘÁDKY (ROWS)
SLOUPCE (COLUMNS)
SLOUPEC (COLUMN)
SVYHLEDAT (VLOOKUP)
TRANSPOZICE (TRANSPOSE)
VVYHLEDAT (HLOOKUP)
VYHLEDAT (LOOKUP)
ZÍSKATKONTDATA (GETPIVOTDATA)
ZVOLIT (CHOOSE)
Související články
Ke stažení

Článek nemá za cíl byt kompletní příručkou. Průběžně jen doplňují jako ostatní články.

FORMULATEXT (FORMULATEXT) ve VBA

Zapsaní funkce do buňky


Range("B4").Formula = "=FORMULATEXT(A4)"

Range("B5").FormulaLocal = "=FORMULATEXT(A5)"

Využití WorksheetFunction

Bohužel WorksheetFunction pro FORMULATEXT neexistuje, ale lze využít:


    Pokus = Range("A4").Formula
    MsgBox (Pokus)

    Pokus = Range("B23").FormulaR1C1
    MsgBox (Pokus)

Poznámky a další příklady

Funkce si můžeme zobrazovat ve formátu A1 (Formula) nebo R1C1 (FormulaR1C1).

HYPERTEXTOVÝ.ODKAZ (HYPERLINK)

Zapsaní funkce do buňky


Range("B4").Formula = "=HYPERLINK("http://JakNaExcel.cz")"

Range("B5").FormulaLocal = "=HYPERTEXTOVÝ.ODKAZ("http://JakNaExcel.cz")"

Využití WorksheetFunction, nutno použít jiné kód:

Pro vložení a smazání


 ActiveSheet.Hyperlinks.Add anchor:=Range("B15"), Address:="http://JakNaExcel.cz", ScreenTip:="Nej web", TextToDisplay:="Top web o Excel ;)"		
		
  Selection.Hyperlinks.Delete

Poznámky a další příklady

V přípravě.

INDEX (INDEX)

Zapsaní funkce do buňky


Range("B13").Formula = "=INDEX(C6:E10,2,2)"

Range("B15").FormulaLocal = "=INDEX(C6:E10;2;2)"

Využití WorksheetFunction

zjištění hodnoty v řádku a sloupci 2.


x = WorksheetFunction.Index(Range("C6:E10"), 2, 2)
MsgBox x

budu-li chtít ošetřit...


' Dim
On Error Resume Next
x = WorksheetFunction.Index(Range("C6:E10"), 2, 2)
If Err = 0 Then
     MsgBox x
Else
     MsgBox "Nenalezeno."
     Err.Clear
End If
On Error GoTo 0

Poznámky a další příklady

V přípravě.

NEPŘÍMÝ.ODKAZ (INDIRECT)

Zapsaní funkce do buňky


Range("B7").Formula = "=INDIRECT(B4)"

Range("B8").FormulaLocal = "=NEPŘÍMÝ.ODKAZ(B4)"

Využití WorksheetFunction

protože Excel opět nemá k dispozici WorksheetFunction Indirect, ale lze obejít, například:


x = Rang(Range("B4").Value).Value
MsgBox x

Poznámky a další příklady

V přípravě.

ODKAZ (ADDRESS)

Zapsaní funkce do buňky


Range("B4").Formula = "=ADDRESS(6;2;1)"

Range("B5").FormulaLocal = "=ODKAZ(6;2;2)"

Využití WorksheetFunction


a = ActiveCell.Address
MsgBox (a)

a = ActiveCell.Address(ReferenceStyle:=xlR1C1)
MsgBox (a)

Poznámky a další příklady

V přípravě.

POČET.BLOKŮ (AREAS)

Jak zapsat funkci do buňky

Zapsaní funkce do buňky


Range("B13").Formula = "=AREAS((B5:D5,F5:F7))"

Range("B14").FormulaLocal = "=POČET.BLOKŮ(B5:D5)"

Využití WorksheetFunction


v přípravě

Poznámky a další příklady

V přípravě.

POSUN (OFFSET)

Zapsaní funkce do buňky


Range("B13").Formula = "=OFFSET(B4,1,1)"

Range("B14").FormulaLocal = "=POSUN(B4;1;2)"

Využití WorksheetFunction

Jak využít offset pro buňku, neboli sse posunout o patřičný pořet řádku a sloupců.


x = Range("B4").Offset(1, 2)
MsgBox (x)

Poznámky a další příklady

V přípravě.

POZVYHLEDAT (MATCH)

Zapsaní funkce do buňky


Range("C10").Formula = "=MATCH(B10,B5:B8,0)"

Range("C11").FormulaLocal = "=POZVYHLEDAT(B11;B5:B8;0)"

Využití WorksheetFunction

Jak ajít pozici požadované hodnoty:

x = WorksheetFunction.Match(Range("B11"), Range("B5:B8"), 0)
MsgBox x

Poznámky a další příklady

V přípravě.

RTD (RTD)

V přípravě.

ŘÁDEK (ROW)

Zapsaní funkce do buňky


Range("B4").Formula = "=ROW()"

Range("B5").FormulaLocal = "=ŘÁDEK(B5)"

Range("B6").FormulaLocal = "=ŘÁDEK()"

Využití WorksheetFunction


x = ActiveCell.Row
MsgBox (x)

Poznámky a další příklady

V přípravě.

ŘÁDKY (ROWS)

Zapsaní funkce do buňky


Range("B9").Formula = "=ROWS(B5:B7)"

Range("B10").FormulaLocal = "=ŘÁDKY(B5:B7)"

Range("B11").FormulaLocal = "=ŘÁDKY($B$5:$B$7)"

Využití WorksheetFunction


x = Range("B7").Row - Range("B5").Row + 1
MsgBox (x)

Poznámky a další příklady

V přípravě.

SLOUPCE (COLUMNS)

Když potřebuji vložit vzorec pro zjjištění počtu sloupců v danné oblasti:

Zapsaní funkce do buňky


Range("B8").Formula = "=COLUMNS(B5:F6)"

Range("B9").FormulaLocal = "=SLOUPCE(B5:F6)"

Využití WorksheetFunction

Protože WorksheetFunction neexistuje, lze využít zjištění sloupce přes Column.


x = Range("F6").Column - Range("B6").Column + 1
MsgBox (x)

Poznámky a další příklady

V přípravě.

SLOUPEC (COLUMN)

Zapsaní funkce do buňky


Range("B4").Formula = "=COLUMN()"

Range("B5").FormulaLocal = "=SLOUPEC()"

Range("B6").FormulaLocal = "=SLOUPEC(B6)"

Využití WorksheetFunction


x = ActiveCell.Column
MsgBox (x)

Poznámky a další příklady

V přípravě.

SVYHLEDAT (VLOOKUP)

Zapsaní funkce do buňky

Využití WorksheetFunction

najít odpovídající hodnotu:


     x = WorksheetFunction.VLookup(Range("C15"), Range("B5:F13"), 2, False)
     MsgBox x

    On Error Resume Next
    x = WorksheetFunction.VLookup(Range("C15"), Range("B5:F13"), 2, False)
    y = WorksheetFunction.VLookup(Range("C15"), Range("B5:F13"), 4, False)
    If Err = 0 Then
        MsgBox x
        MsgBox y
    Else
        MsgBox "Nenalezeno."
        Err.Clear
    End If
    On Error GoTo 0

Poznámky a další příklady

V přípravě.

TRANSPOZICE (TRANSPOSE)


Dim myarray As Variant

myarray = Array(1, 2, 3, 4, 5)
Range("A20:E20").Value = myarray
Range("B21:B25").Value = Application.Transpose(myarray)
Range("C21:C25").Value = Application.Transpose(Array(1, 2, 3, 4, 5))

Poznámky a další příklady

V přípravě.

VVYHLEDAT (HLOOKUP)

Zapsaní funkce do buňky


Range("C12").Formula = "=HLOOKUP(G11,B4:G8,2,TRUE)"

Range("C13").FormulaLocal = "=VVYHLEDAT(G11;B4:G8;3;PRAVDA)"

Využití WorksheetFunction


     x = WorksheetFunction.HLookup(Range("G11"), Range("B4:G8"), 2, False)
     MsgBox x

    On Error Resume Next
    x = WorksheetFunction.HLookup(Range("G11"), Range("B4:G8"), 2, False)
    y = WorksheetFunction.HLookup(Range("G11"), Range("B4:G8"), 3, False)
    If Err = 0 Then
        MsgBox x
        MsgBox y
    Else
        MsgBox "Nenalezeno."
        Err.Clear
    End If
    On Error GoTo 0

Poznámky a další příklady

V přípravě.

VYHLEDAT (LOOKUP)

Zapsaní funkce do buňky


Range("C14").Formula = "=LOOKUP(B14;$B$5:$B$10;$C$5:$C$10)"

Range("C16").FormulaLocal = "=VYHLEDAT(B16;$B$5:$B$10;$C$5:$C$10)"

Využití WorksheetFunction


    On Error Resume Next
    x = WorksheetFunction.Lookup(Range("B16"), Range("B5:B10"))
    y = WorksheetFunction.Lookup(Range("B16"), Range("B5:B10"), Range("C5:C10"))
    If Err = 0 Then
        MsgBox x
        MsgBox y
    Else
        MsgBox "Nenalezeno."
        Err.Clear
    End If
    On Error GoTo 0

Poznámky a další příklady

V přípravě.

ZÍSKATKONTDATA (GETPIVOTDATA)

Zapsaní funkce do buňky


Range("F12").Formula = "=GETPIVOTDATA(""Zisk"",$F$4,""Prodavač"",""Fantomas"")"

Range("F15").FormulaLocal = "=ZÍSKATKONTDATA(""Zisk"";$F$4;""Prodavač"";""Pavel"")"

Využití WorksheetFunction


v přípravě

Poznámky a další příklady

V přípravě.

ZVOLIT (CHOOSE)

Zapsaní funkce do buňky


Range("B11").Formula = "=CHOOSE($C$9,C5,C6,C7)"

Range("B12").FormulaLocal = "=ZVOLIT($C$9;C5;C6;C7)"

Využití WorksheetFunction


x = WorksheetFunction.Choose(Range("C9"), Range("C5"), Range("C6"), Range("C7"))
MsgBox x

Poznámky a další příklady

V přípravě.

Související články s funkci INDEX

Další články související se statistickými funkcemi:

Přehled vyhledávacích funkcí
POZVYHLEDAT

Ke stažení

Soubor v přípravě.

Závěrem

Využíváte nějaký trik s funkcí INDEX, můžete zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 11:06

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

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ů.