Live-Forum - Die aktuellen Beiträge
Datum
Titel
14.10.2025 15:55:14
wenn zwei Werte aus Liste übereinstimmen, dann wahr
14.10.2025 14:57:29
Wenn Datum in Ferienzeit dann Name angeben
14.10.2025 09:39:44
VBA - Excel speichern und schließen (Laufzeitfehler 91)
13.10.2025 19:31:31
VBA Formel mit Bezug auf Überschrift anstatt Spalte
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1368to1372
1368to1372
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Esffektive Suche mit Application.Match ?

Forumthread: Esffektive Suche mit Application.Match ?

Esffektive Suche mit Application.Match ?
21.07.2014 13:56:29
Kuttel
Hallo an die Community,
da die Suche von Werten in einer Tabelle bei meinem Projekt eine zentale Rolle spielt, bin ich sehr an einer effektiven Suche interssiert. Bisher habe ich ein Makro, das Zelle für Zelle mit:
For i = 1 To Suchbereich.Columns.Count
If Suchbereich.Zelle(3,i).Value = Suchwert then
...
Exit For
...
End If
Next i
nach einem Wert sucht.
Wäre es vielleicht schneller/effektiver die Funktion Application.Match einzusetzen? Hinweis: die Werte in der Tabelle sind nicht geordnet. Außerdem ignoriert Application.Match Groß- und Kleinschreibung. Wie müsste man eine Suche mit Application.Match programmieren, die eine Groß- und Kleinschreibung beachtet und ggf. bei fehlender Übereinstimmung weiter sucht (ab da wo sie einen möglichen Treffer gefunden hat).
Mich würde interessieren, ob das dann am Ende schneller/effektiver ist als das Makro.
Danke und Gruß
Kuttel

Anzeige

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Esffektive Suche mit Application.Match ?
21.07.2014 14:49:21
Nepumuk
Hallo,
deine Suche in Zellen ist die langsamste Methode die es überhaupt gibt. Um 1.000.000 Zellen zu durchsuchen benötigt mein Rechner 8 Sekunden. Das selbe in einem Array dauert gerade mal 0,1 Sekunde.
Schau mal hier: http://www.office-loesung.de/ftopic641735_0_0_asc.php die Idee, finde ich gut.
Gruß
Nepumuk

Anzeige
AW: Esffektive Suche mit Application.Match ?
21.07.2014 15:17:40
UweD
Hallo
so?
Sub Ersetzen()
Dim Sh As Worksheet
Dim C
Dim SuchWert As String
Dim ErsetzWert As String
Dim SuchBereich As Range
Set Sh = ActiveSheet
Set SuchBereich = Sh.Rows(3)
SuchWert = "Alt"
ErsetzWert = "Neu"
Set C = SuchBereich.Find(SuchWert, LookIn:=xlValues)
If Not C Is Nothing Then
Do
'Mach was damit... z.B.
C.Value = ErsetzWert
Set C = SuchBereich.FindNext(C)
Loop While Not C Is Nothing
End If
End Sub
Gruß UweD

Anzeige
AW: Esffektive Suche mit Application.Match ?
21.07.2014 15:21:53
Nepumuk
Hallo Uwe,
hast du schon mal einen Vergleichstest gemacht? Also Find in der Tabelle versus lineares Durchsuchen eines Arrays? Da schneidet die Find-Methode nicht gut ab.
Gruß
Nepumuk

AW: Esffektive Suche mit Application.Match ?
21.07.2014 15:20:22
Daniel
Hi
wenn die Daten nicht sortiert sind und Gross-/Kleinschreibung beachtet werden muss, wäre die effetkivste Suche mit Hilfe der .FIND-Funktion
Set Zelle = Suchbereich.Rows(3).Find(What:=Suchwert, LookAt:=xlwhole, LookIn:=xlvalues,  _
MatchCase:=true)
If Zelle is Nothing then Msgbox Suchwert & " nicht vorhanden."
Application.Match ist dann sinnvoll, wenn die Daten sortiert sind, weil es dann eine wesentlich schnellere Suchmethode verwenden kann.
Wenn du denselben Suchbereich sehr oft verwenden willst, ohne dass sich die Inhalte verändern, kannst du auch so vorgehen:
1. Lese den Suchbereich in ein Array ein
2. Gehe mit einer Schleife durch das Array und vergleiche die Werte.
Schleifen über Arrays sind sehr schnell (ähnlich schnell wie die .Find-funktion) Zeit sparst du gegenüber .Find, weil du das Array nicht für jede Suche erneut einlesen musst:
Dim arrSuche
dim i as long
arrSuche = Suchbereich.Rows(3)
for i = 1 to Ubound(arrSuche, 2)
if arrSuche(i, 2) = Suchwert Then Exit For
Next
gruß Daniel

Anzeige
AW: Esffektive Suche mit Application.Match ?
21.07.2014 16:24:29
Kuttel
Vielen Dank zunächst an alle für die Tipps!
Gibt es beim Einlesen des Arrays eine Größenbeschränkung? Ist es problematisch, wenn ich einen Bereich mit ca. 120 Zeilen x 250 Spalten einlese oder vielleicht sogar ein ganzes Tabellenblatt (Zeilen und Spalten gehen in die Tausende...)?
Bringt die For-Each-Schleife eigentlich Vorteile/Schnelligkeit gegenüber der For-Next (bei Arrays bzw. in Range-Objekten)?
Danke und Gruß

Anzeige
AW: Esffektive Suche mit Application.Match ?
21.07.2014 17:25:47
Daniel
Hi
ja, bei den Arrays gibt es schon eine Grössenbeschränkung, aber die liegt irgendwo in den Millionen
bei Objekten ist For Each schneller als For Next (und oft auch die einzige Möglichkeit).
du kannst For Each auch auf Arrays anwenden, das hat aber den Nachteil, dass du dann nur den Wert kennst, aber nicht die Position im Array.
Gruß Daniel

Anzeige
Der 'Nachteil' ist relativ zu verstehen, denn, ...
21.07.2014 18:25:27
Luc:-?
…wenn man einen IndexZähler mitlaufen lässt, Daniel (& Kuttel),
hat man auch die Position. Dabei ist bei Matrizen allerdings zu beachten, dass im Ggsatz zu den entsprd ZellBereichen (Durchlauf Zeile für Zeile) DatenFelder (Arrays) normalerweise Spalte für Spalte durchlaufen wdn.
Außerdem gibt's ja auch noch die VBA-Entsprechung eines assoziativen Arrays, also das Dictionary, bei dem ein Index als Key festgelegt wdn kann.
Gruß, Luc :-?

Anzeige
AW: Der 'Nachteil' ist relativ zu verstehen, denn, ...
21.07.2014 18:34:27
Kuttel
Huch,
ich muss gestehen, dass Deine Ausführungen leider meinen Excel/VBA-Horizont übersteigen. Ein zweidimensionales Array kann ich doch auch mit zwei verschachtelten For-Next-Schleifen horizontal durchsuchen - oder ist das gerade wieder ineffektiv?
Diese Dictionaries und Collections sind leider noch Böhmische Dörfer für mich. Mit Collections habe ich nur ganz wenig gearbeitet, mit Dictionaries noch gar nicht. Lohnt sich das Befassen damit? Bringt es merkliche Unterschiede in Sachen Geschwindigkeit?
Gruß
Kuttel

Anzeige
AW: Der 'Nachteil' ist relativ zu verstehen, denn, ...
21.07.2014 19:42:51
Daniel
Hi
Die 2-d-Arrays kannst du problemlos mit zwei Schleifen durchlaufen.
Das for-each-in dürfte nicht viel bringen, wenn du zusätzlich noch die Zähler mitlaufen lässt (höchstens eine zusätzliche Fehlerquelle)
Dictionarys bzw. Assoziative Arrays sind im Prinzip eindimensionale Arrays mit einem Freitextindex.
Damit kann man schon tolle Sachen machen, aber es hängt von der konkreten Aufgabenstellung ab.
Gruß Daniel

Anzeige
AW: Der 'Nachteil' ist relativ zu verstehen, denn, ...
21.07.2014 22:08:44
Nepumuk
Hallo,
in der Zeit, die du brauchst um ein größeres Array in ein Dictionary zu packen, kannst du das Array 10mal durchsuchen.
Ich würde auf alle Fälle mal testen, wie es vom Zeitverhalten aussieht wenn du direkt in der Exceltabelle eine SQL-Abfrage machst. Dazu muss aber die Tabelle entsprechend aufgebaut sein (erste Zeile eine eindeutige Überschrift !!!) und du kannst immer nur Spaltenweise suchen.
Eine sehr effektive Methode ist es eine virtuelle Datenbanktabelle zu erstellen, denn SQL-Abfragen darin sind erheblich schneller als alles was VBA zu bieten hat. Dazu muss aber wie gesagt die Tabelle genau die vorgegebene Struktur haben.
Gruß
Nepumuk
Anzeige
;
Anzeige

Infobox / Tutorial

Effektive Suche mit Application.Match in VBA

Schritt-für-Schritt-Anleitung

Um die Funktion Application.Match in VBA effektiv zu nutzen, folge diesen Schritten:

  1. Öffne den VBA-Editor: Drücke ALT + F11, um den VBA-Editor zu öffnen.

  2. Erstelle ein neues Modul: Rechtsklicke im Projektfenster auf "VBAProject" und wähle Einfügen > Modul.

  3. Schreibe den Code: Verwende den folgenden Code, um die Application.Match-Funktion zu implementieren:

    Sub SucheMitMatch()
   Dim SuchWert As String
   Dim SuchBereich As Range
   Dim Ergebnis As Variant

   SuchWert = "DeinSuchwert"
   Set SuchBereich = Sheets("DeinBlatt").Range("A1:A100")

   Ergebnis = Application.Match(SuchWert, SuchBereich, 0)

   If Not IsError(Ergebnis) Then
       MsgBox "Wert gefunden in Zeile: " & Ergebnis
   Else
       MsgBox "Wert nicht gefunden."
   End If
End Sub

  4. Führe das Makro aus: Drücke F5, um das Makro auszuführen und die Suche zu testen.

Häufige Fehler und Lösungen

  • Problem: #N/A Fehler bei Application.Match.

    • Lösung: Stelle sicher, dass der Suchwert im Suchbereich existiert. Achte auf Groß- und Kleinschreibung, da Application.Match sie ignoriert.

  • Problem: Die Funktion findet den Wert nicht.

    • Lösung: Überprüfe, ob der Suchbereich korrekt definiert ist und ob der Suchwert tatsächlich im Bereich enthalten ist.

Alternative Methoden

Wenn Application.Match nicht die gewünschte Leistung erbringt, kannst du folgende Alternativen in Betracht ziehen:

  1. Die .Find-Methode:

    Dim Zelle As Range
Set Zelle = SuchBereich.Find(SuchWert, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Not Zelle Is Nothing Then
   MsgBox "Wert gefunden in Zelle: " & Zelle.Address
End If

  2. Durchsuchen eines Arrays: Lade den Suchbereich in ein Array und durchsuchen es:

    Dim arrSuche As Variant
arrSuche = SuchBereich.Value
For i = LBound(arrSuche) To UBound(arrSuche)
   If arrSuche(i, 1) = SuchWert Then
       MsgBox "Wert gefunden in Zeile: " & i
       Exit For
   End If
Next i

Praktische Beispiele

Hier sind einige praktische Beispiele, wie du Application.Match in verschiedenen Szenarien verwenden kannst:

  • Beispiel für eine Groß-/Kleinschreibung sensitive Suche:

    Sub SucheMitMatchCase()
  Dim SuchWert As String
  Dim SuchBereich As Range
  Dim Ergebnis As Variant

  SuchWert = "DeinSuchwert"
  Set SuchBereich = Sheets("DeinBlatt").Range("A1:A100")

  Ergebnis = Application.Match(SuchWert, SuchBereich, 0)

  If Not IsError(Ergebnis) Then
      MsgBox "Wert gefunden in Zeile: " & Ergebnis
  Else
      MsgBox "Wert nicht gefunden."
  End If
End Sub

Tipps für Profis

  • Nutze Arrays für Geschwindigkeitsvorteile: Wenn du häufig auf einen großen Datenbereich zugreifst, lade die Daten einmal in ein Array und suche darin. Dies kann die Leistung erheblich verbessern.

  • Kombiniere Application.Match mit IFERROR: Um Fehler zu handhaben, kannst du IFERROR verwenden, um die Fehlermeldungen abzufangen und benutzerfreundliche Meldungen auszugeben.

FAQ: Häufige Fragen

1. Frage
Wie viele Zellen kann ich mit einem Array einlesen?
Antwort: Es gibt eine Größenbeschränkung, die in den Millionen liegt, also kannst du problemlos große Bereiche einlesen.

2. Frage
Bringt es Vorteile, Dictionaries oder Collections zu verwenden?
Antwort: Ja, Dictionaries bieten eine schnellere Zugriffsmöglichkeit auf Daten, besonders wenn du häufige Suchvorgänge durchführst.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige