VBA-Code zum Suchen von Zellen in einem Bereich

Die Methode Find der Klasse Range wird verwendet, um Zellen zu finden, die einen bestimmten Wert oder ein bestimmtes Format in einem Bereich enthalten.

Im Arbeitsblatt suchen

In seiner Grundform wird Find wie der unten stehende Code aufgerufen, der die gefundene Zelle auswählt. Der Code funktioniert nur, wenn der Wert tatsächlich auf dem Arbeitsblatt vorhanden ist.


ActiveSheet.UsedRange.Find(What:="x").Select

Anmerkung: Um den Beispielcode kurz zu halten, verwenden wir die ActiveSheet Methode zur Bereitstellung der übergeordneten Worksheet Objekt, dessen Range Klasse verwendet wird. In echtem Code ist die Verwendung explizit deklarierter und zugewiesener Objektvariablen vorzuziehen, im vorliegenden Fall: Dim wsActiveSheet As Worksheet: Set wsActiveSheet = ActiveSheet , sehr wahrscheinlich mit einem anderen Objektzuweisungsausdruck.

Das Objekt der Klasse Range wird hier von der Prozedur Worksheet UsedRange bezogen.

Die Prozedur gibt ein Objekt vom Typ Range zurück, das optional einer Variablen rngFound zugewiesen wird. Im Allgemeinen werden Sie eine Variable verwenden, hier: strWhat, um den Parameter What zuzuweisen.


Dim strWhat As String: strWhat = 
Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:=strWhat)

Die Methode Find gibt Nothing zurück, wenn keine Übereinstimmung gefunden wird.

Anmerkung: Die Find-Methode hat keine Auswirkungen auf die Auswahl oder die aktive Zelle.

Im Bereich finden

Suche nach Wert in Spalte

Mit dem nachstehenden Code können Sie eine Zelle finden, die den Wert in einer bestimmten Spalte enthält.


Dim rngColumn As Range
Set rngColumn = ActiveSheet.Columns(3)
Dim rngFound As Range
Set rngFound = rngColumn.Find(What:="x").Select

Zeile nach Wert suchen

Verwenden Sie den folgenden Code, um eine Zelle zu finden, die den Wert in einer Zeile enthält.


Dim rngRow As Range
Set rngRow = ActiveSheet.Rows(8)
Dim rngFound As Range
Set rngFound = rngRow.Find(What:="x")

Was: Suchkriterien angeben

Optionen des Suchdialogs — Die Parameter der Excel-Suchmethode entsprechen weitgehend den Optionen des Dialogfelds Suchen.

Was finden

Die Daten, nach denen gesucht werden soll. Kann eine Zeichenfolge oder ein beliebiger Microsoft Excel-Datentyp sein. Einige Hinweise zu geeigneten Parameterwerten:

Bei der Suche numerische Werte verwenden. Lookin:=xlFormulas und LookAt:=xlWhole
Bei der Suche Datumswerte verwenden. Lookin:=xlFormulas und LookAt:=xlWhole. Das gelieferte Datum sollte wie folgt aussehen 25/09/2024 die Sie erhalten können, mit FormatDateTime(dtDate, vbGeneralDate)

Warnung:

Bei der Suche nach numerischen Werten, LookAt:=xlPart kann dazu führen, dass Sie mehr Nummern finden, als Sie wollen: Wenn Sie nach 1 suchen, werden Sie auch 301, 12 und so weiter erhalten.
Wenn die Spalten kleiner sind, wird in den Zellen möglicherweise nicht der Wert, sondern ### angezeigt. Sie können sicherer sein, wenn Sie mit Lookin:=xlFormulas suchen.

After (Nach): Startzelle für die Suche

Wenn After angegeben wurde, beginnt die Suche nach dieser Zelle; die angegebene Zelle wird erst durchsucht, wenn die Methode wieder zu dieser Zelle zurückkehrt.

Anmerkung: Wenn Sie dieses Argument nicht angeben, beginnt die Suche nach der Zelle in der oberen linken Ecke des Bereichs A1. Dies ist anders als bei der Verwendung des Dialogfelds Suchen die bei der ActiveCell beginnt.

Fehler: Wenn das Argument Nach verwendet wird, muss es sich um eine einzelne Zelle im Bereich handeln. Wenn nicht, erhalten Sie Fehler 13: Nicht übereinstimmende Typen.

Der folgende Code startet die Suche nach der aktiven Zelle und ahmt den Suchdialog nach


Dim rngFound As Range
Set rngFound = ws.UsedRange.Find(What:="x", After:=ActiveCell)

Nachschlagen: Formeln, Werte, Notizen, Kommentare

Kann eine der folgenden XlFindLookIn-Konstanten sein: xlFormulas (Formeln), xlValues (Werte), xlComments (Notizen), oder xlCommentsThreaded (Kommentare).


Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", LookIn:=xlComments)

xlFormulas vs xlValues (xlFormeln vs. xlWerte)

Der Unterschied zwischen den gefundenen Formeln oder Werten kann experimentell beobachtet werden. Geben Sie in A1 den Wert x und in B1 die Formel =A1 ein. Im Suchen-Dialog drücken Sie Find All. Bei Auswahl von Formeln wird nur A1 gefunden, während bei Werten sowohl A1 als auch B1 gefunden werden.

Schlussfolgerung:

Formeln prüft, was die Zelle enthält und was in der Formelleiste verfügbar ;
Die Werte stimmen mit dem überein, was als Wert in der Gitterzelle angezeigt wird.

Warnung: Die Einstellungen für LookIn, LookAt und SearchOrder werden bei jeder Verwendung dieser Methode gespeichert. Wenn Sie beim nächsten Aufruf der Methode keine Werte für diese Argumente angeben, werden die gespeicherten Werte verwendet. Das Setzen dieser Argumente ändert die Einstellungen im Dialogfeld Suchen, und das Ändern der Einstellungen im Dialogfeld "Suchen" ändert die gespeicherten Werte, die verwendet werden, wenn Sie die Argumente weglassen.

Ratschläge: Um Probleme zu vermeiden, setzen Sie LookIn, LookAt und SearchOrder jedes Mal explizit, wenn Sie die Find Methode verwenden.

Anmerkung: Leider können die aktuellen Werte, die in Find verwendet werden, nicht abgerufen werden (z.B. über die Einstellung der Anwendungseigenschaften), so dass Sie sie nach der Verwendung nicht wiederherstellen können. Glücklicherweise werden die Werte nach dem Schließen von Excel auf ihre Standardwerte zurückgesetzt.

LookAt: Gesamten Zellinhalt abgleichen

Kann eine der folgenden XlLookAt-Konstanten sein: xlWhole oder xlPart. Verwenden Sie xlWhole, wenn Sie eine Zelle mit einem bestimmten Text suchen müssen.

Fügen Sie MatchCase:=True hinzu, wenn Sie eine exakte Übereinstimmung im Bereich suchen müssen (Groß- und Kleinschreibung wird berücksichtigt).


Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", LookAt:=xlWhole, MatchCase:=True)

Verwendung von Wildcards

In Excel können Sie im Argument Suchen nur begrenzt Platzhalterzeichen verwenden:

ein Fragezeichen (?) entspricht einem Zeichen, wenn es in einer Zeichenkette enthalten ist;
Ein Fragezeichen am Anfang oder Ende passt auch, wenn es mehr als 1 Zeichen gibt.
Ein Sternchen (*) entspricht einer beliebigen Folge von Zeichen.

Der folgende Code findet zum Beispiel jede Zelle, die mit "x" übereinstimmt.


Set rngFound = ActiveSheet.UsedRange.Find(What:="?x*")

Mit der Prozedur TestFindAll (unten auf der Seite) können Sie feststellen, welche Ergebnisse bei gegebenen Suchargumenten zu erwarten sind. Sie schreibt die Address des Bereichs in das Fenster "Sofort": $B$1:$C$1,$C$2

Basisdaten, um zu prüfen, welche Zellen die Kriterien erfüllen

Suchreihenfolge: Zeilen oder Spalten

Kann eine der folgenden XlSearchOrder-Konstanten sein: xlByRows oder xlByColumns.

Suche nach Spalten bedeutet, dass zuerst alle Zellen in Spalte A, dann in Spalte B usw. gesucht werden.
Suche nach Zeile bedeutet, dass zuerst alle Zellen in Zeile 1, dann in Zeile 2 usw. durchsucht werden.


Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", SearchOrder:=xlByRows)

SearchDirection: Weiter suchen

Die Suchrichtung zusammen mit SearchOrder bestimmt, was die nächsten Zellen sein werden. Die folgende Kombination wird in den Spalten nach oben wandern, z.B. unter der Annahme, dass UsedRange letzte Zelle B6 ist: B6, B5, B4...


Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", SearchOrder:=xlByColumns, _
											SearchDirection:=xlPrevious)

Bereichssuche nach mehreren Kriterien

Um Zellen zu finden, die komplizierteren Mustern entsprechen, können Sie eine For Each...Next-Anweisung mit verwenden:

den Like-Operator,
Boolesche Tests anwenden, die mit Und, Oder usw. kombiniert werden.

Der folgende Code sucht zum Beispiel nach allen Zellen im Bereich A1:C5, die eine Schriftart verwenden, deren Name mit den Buchstaben Cour beginnt. Wenn Microsoft Excel eine Übereinstimmung findet, ändert es die Schriftart in Times New Roman.


Dim rngCells As Range
Set rngCells = ActiveSheet.UsedRange.Cells
Dim rngCell As Range
Dim rngFound As Range
For Each rngCell In rngCells
	If rngCell.Font.Name Like "Cour*" Then
		Set rngFound = rngCell
		Exit For
	End If
Next rngCell

SucheFormat: Format...

Gibt nur Zellen zurück, auf die eine bestimmte Formatierung angewendet wurde (Schriftart, Hintergrundfarbe,...). Das folgende Beispiel zeigt, dass Sie alle Zellen finden können, die eine Hintergrundfarbe haben, indem Sie einen leeren String für den Parameter What verwenden. Dekommentieren Sie .Color = 65535, um die gelb markierten Zellen zu erhalten. Alternativ können Sie auch nach Zellen filtern, die Font.Bold oder anderen Eigenschaften der Zelle filtern.

Anmerkung: Durch die gleichzeitige Verwendung von What und FindFormat werden nur Zellen gefunden, die beiden Kriterien entsprechen.


Sub Demo()
With Application.FindFormat
	.Clear
'    .Font.Bold = True
	With .Interior
		.Pattern = xlSolid
        '.Color = 65535 'rgbYellow 
	End With
End With
ActiveSheet.UsedRange.Find(After:=ActiveCell, What:="", SearchFormat:=True).Select
Application.FindFormat.Clear
End Sub

Vergessen Sie das nicht: Clear die FindFormat (sowohl vor als auch) nach der Ausführung der Find.

MatchByte: z.B. Japanisch und Chinesisch

Wird nur verwendet, wenn Sie die Doppelbyte-Sprachunterstützung ausgewählt oder installiert haben. True, damit Doppelbyte-Zeichen nur mit Doppelbyte-Zeichen übereinstimmen. False, damit Doppelbyte-Zeichen mit ihren Ein-Byte-Entsprechungen übereinstimmen.

Autor: Mark Uildriks

Referenz: Range.Find(Excel) | Microsoft Learn

Durchlaufen Sie Zellen mit „Find“ und „FindNext“, um mehrere Suchergebnisse zu erhalten

Die Methode Range FindNext

Sie können die unten beschriebene Methode verwenden, um die zuvor besprochene Suche zu wiederholen. FindNext setzt eine Suche fort, die mit der Methode Find begonnen wurde. Sie findet die nächste Zelle, die dieselben Bedingungen erfüllt, und es gibt ein Range-Objekt zurück, das diese Zelle darstellt.

Der folgende Code zeigt eine Schleife, die fortgesetzt wird, bis die Suche abgeschlossen ist. Wenn die Suche das Ende des angegebenen Suchbereichs erreicht, kehrt sie an den Anfang des Bereichs zurück. Um die Suche zu beenden, wenn dieser Umbruch auftritt, speichern Sie die Adresse der ersten gefundenen Zelle, und testen Sie dann jede weitere gefundene Zelladresse mit dieser gespeicherten Adresse.


Dim rngToSearch As Range: Set rngToSearch = ActiveSheet.UsedRange
Dim strWhat As String: strWhat = "test"
Dim rngFound As Range
Set rngFound = rngToSearch.Find(What:=strWhat)
If Not rngFound Is Nothing Then
	Dim FirstFoundCell As String
	FirstFoundCell = rngFound.Address
	Do
		Debug.Print rngFound.Address
		Set rngFound = rngToSearch.FindNext(After:=rngFound)
	Loop While FirstFoundCell <> rngFound.Address
End If

Fehlerbehandlung

Bevor die eigentliche Suche fortgesetzt wird, verhindert der obige Code den Fehler, indem er überprüft, ob die Variable instanziiert wurde: If Not rngFound Is Nothing Then. Wenn dies nicht geschieht, wird der Code früher oder später wenn Sie einige der Prozeduren der Klasse anwenden, auf den Fehler 91 stoßen: Objektvariable oder With-Block-Variable nicht gesetzt.

Das Argument After muss eine einzelne Zelle im Bereich sein. Wenn nicht, erhalten Sie error 13: Type mismatch.

Fehlerbehandlung hinzufügen Insbesondere wenn Sie Ihr Makro auch anderen Benutzern zur Verfügung stellen möchten, ist es sinnvoll, Code zur Fehlerbehandlung hinzuzufügen.

A Range FindAll-Methode

Es gibt keine eingebaute FindAll-Methode. Auf der Grundlage des obigen Codes stellen wir jedoch eine FindAll-Funktion bereit, die einen Bereich mit allen Zellen zurückgibt, die den Suchkriterien entsprechen, und eine Prozedur, um FindAll mit verschiedenen Einstellungen zu testen (siehe Code unten).

Code VBA-Bibliothek Der Code ist auch enthalten in der Code VBA Editor-Zusatzmodul die Sie hier herunterladen können.


Public Function FindAll(Range As Range, What As Variant, _
    Optional LookIn As XlFindLookIn = xlValues, _
    Optional LookAt As XlLookAt = xlWhole, _
    Optional SearchOrder As XlSearchOrder = xlByRows, _
    Optional MatchCase As Boolean = False) As Range
    Dim FoundCell As Range, FirstFound As Range, ResultRange As Range
    Dim LastCell As Range: Set LastCell = Range.Cells(Range.Cells.Count)
    Set FoundCell = Range.Find(What:=What, After:=LastCell, LookIn:=LookIn, LookAt:=LookAt, _
                                SearchOrder:=SearchOrder, MatchCase:=MatchCase)
    If Not FoundCell Is Nothing Then
        Set FirstFound = FoundCell
        Set ResultRange = FoundCell
        Set FoundCell = Range.FindNext(After:=FoundCell)
        Do Until (FoundCell.Address = FirstFound.Address)
            Set ResultRange = Application.Union(ResultRange, FoundCell)
            Set FoundCell = Range.FindNext(After:=FoundCell)
        Loop
    End If
    Set FindAll = ResultRange
End Function


Sub TestFindAll(What As Variant, _
                Optional LookIn As XlFindLookIn = xlValues, _
                Optional LookAt As XlLookAt = xlWhole, _
                Optional SearchOrder As XlSearchOrder = xlByRows, _
                Optional MatchCase As Boolean = False)
    Dim rngUsedRange As Range: Set rngUsedRange = ActiveSheet.UsedRange
    Dim rngSearchResults As Range
    Set rngSearchResults = FindAll(Range:=rngUsedRange, What:=What, LookIn:=LookIn, _
                            LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
    Debug.Print rngSearchResults.Address
    rngSearchResults.Select
End Sub