Finden Sie einen Wert in einer Spalte mit Excel VBA

1. Finden Sie eine Zeichenfolge/einen Wert in einer Spalte mit der Funktion Find() in VBA
2. Finden Sie eine Zeichenfolge/einen Wert in einer Spalte mit der Funktion Match() in VBA
3. Suchen Sie eine Zeichenfolge/einen Wert in einer Spalte mithilfe von Schleifen in VBA
4. Suchen einer Zeichenfolge in mehreren Spalten mithilfe von Schleifen in VBA

Das Erstellen von Automatisierungstools in Excel erfordert mehrere String-Matching-Funktionen wie Instr(), CStr(), Split() usw. Diese Funktionen finden einen Teilstring innerhalb eines Strings. Wenn Sie jedoch mit Zeichenketten/Werten über Spalte/n arbeiten, können Sie diese Funktionen nicht verwenden, da es sich um Funktionen mit einer einzelnen Zeichenfolge handelt.

In diesem Artikel werden drei Techniken zum Suchen einer Zeichenfolge oder eines Werts in einer Spalte demonstriert. Der return-Wert ist die Zeilennummer, in der sich die Zielzeichenfolge befindet.

String-Suchtechniken:

1. Verwenden Sie die Funktion Find()
2. Mit der Match-Funktion
3. Verwendung von Schleifen

Für den gesamten Artikel dient dieses virtuelle Blatt als Referenzblatt.

Sheet1-Werte:

  |   A     | B |       C      |  D  |
1 | Apple   | 4 | Philippines  |  3  |
2 | Orange  | 3 | Mexico       |  5  |
3 | Banana  | 6 | Thailand     |  3  |
4 | Carrot  | 7 | Saudi Arabia |  7  |
5 | Grapes  | 5 | U.S.A.       |  1  |
6 | Pear    | 3 | Japan        |  0  |
7 | Citrus  | 9 | Mexico       |  3  |
8 | Pomelo  | 2 | Taiwan       |  4  |
9 |         | 3 | Singapore    |  7  |
10|         | 4 | Mexico       |  8  |

Finden Sie eine Zeichenfolge/einen Wert in einer Spalte mit der Funktion Find() in VBA

Syntax:

Range.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase)

Parameter:

Der folgende Codeblock zeigt die Verwendung der Funktion Find(), um die Zeile der zu durchsuchenden Zeichenfolge zurückzugeben.

Function SearchStr(str As String) As Range

Dim wb As Workbook
Dim s1 As Worksheet
Dim rng As Range

Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")

Set rng = s1.Columns("A:A")
Set SearchStr = rng.Find(What:=str, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)

End Function

Sub BananaSearch()

If SearchStr("Banana") Is Nothing Then
    Debug.Print "Not in range."

Else
    Debug.Print "Found at row: " & SearchStr("Banana").Row

End If

End Sub

Sub MelonSearch()

If SearchStr("Melon") Is Nothing Then
    Debug.Print "Not in range."

Else
    Debug.Print "Found at row: " & SearchStr("Melon").Row

End If
End Sub

BananaSearch-Ausgabe:

Found at row: 3

MelonSearch-Ausgabe:

Not in range

Finden Sie eine Zeichenfolge/einen Wert in einer Spalte mit der Funktion Match() in VBA

Der Hauptunterschied zwischen den Funktionen Find() und Match() besteht darin, dass erstere das Range-Objekt zurückgibt, wo die Zeichenfolge gefunden wird, während letztere die Position zurückgibt, an der die Zeichenfolge eine Übereinstimmung hat.

Syntax:

Application.Match([StringtoSearch],[RangetoSearchIn],[MatchType])

Parameter:

Bemerkungen:

Für [MatchType]-Argumente sind hier die Werte:

Der folgende Codeblock zeigt die Verwendung der Funktion Match(), um die Zeile der zu durchsuchenden Zeichenfolge zurückzugeben.

Function SearchNum(IntToSearch As Integer) As Variant

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")

If Not IsError(Application.Match(IntToSearch, s1.Columns("B:B"), 0)) Then
    SearchNum = Application.Match(IntToSearch, s1.Columns("B:B"), 0)

Else
    SearchNum = "Not Found"
End If

End Function

Sub LookForSix()

Debug.Print "A match is located at row " & SearchNum(6)

End Sub

Sub LookForZero()

Debug.Print "A match is located at row " & SearchNum(0)

End Sub

LookForSix-Ausgabe:

A match is located at row 3

LookForZero-Ausgabe:

A match is located at row Not Found

Suchen Sie eine Zeichenfolge/einen Wert in einer Spalte mithilfe von Schleifen in VBA

Im Gegensatz zu den Funktionen Find() und Match() ermöglicht die Verwendung von Schleifen dem Benutzer, den Code auszuführen. Ein Vorteil von Schleifen ist, dass wir alle Übereinstimmungen aus einem Bereich zurückgeben können. Schleifen werden jedoch beim Umgang mit großen Daten nicht empfohlen, da sie mit jeder Zelle innerhalb der angegebenen Grenzen iterieren.

Der folgende Codeblock demonstriert die Verwendung von Schleifen, um die Zeile der zu durchsuchenden Zeichenfolge zurückzugeben.

Function GetAllRows(str As String) As Object

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Dim coll As Object
Set coll = CreateObject("System.Collections.ArrayList")
Dim i As Long
Dim lrow As Long

lrow = s1.Cells(s1.Rows.Count, 3).End(xlUp).Row

For i = 1 To lrow
    
    If s1.Cells(i, 3) = str Then
        'Add row number in the arraylist
        coll.Add (i)
        
    End If
    
Next i

Set GetAllRows = coll

End Function

Sub FindForMexico()

Dim coll2 As Object

Set coll2 = GetAllRows("Mexico")

Dim j As Integer

For j = 0 To coll2.Count - 1

    Debug.Print "A match is found in row: " & coll2(j)

Next j

End Sub

FindforMexico-Ausgabe:

A match is found in row: 2
A match is found in row: 7
A match is found in row: 10

Suchen einer Zeichenfolge in mehreren Spalten mithilfe von Schleifen in VBA

Die drei obigen Codeblöcke wurden entwickelt, um in nur eine Spalte zu schauen. In seltenen Fällen müssen Sie in mehreren Spalten oder sogar im gesamten Arbeitsblatt nach Zeichenkettenübereinstimmungen suchen. Aber wenn Sie es brauchen, hilft Ihnen der Codeblock unten.

Function GetAllRowsFromSheet(str As String) As Object

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Dim coll As Object
Set coll = CreateObject("System.Collections.ArrayList")

'Create row numbers(i,j)  and last row/column variable in long datatypes.
Dim i, j As Long
Dim lrow, lcol As Long

lrow = s1.Cells(s1.Rows.Count, 3).End(xlUp).Row

lcol = s1.Cells(1, s1.Columns.Count).End(xlToLeft).Column

For j = 1 To lcol
    For i = 1 To lrow
        
        If s1.Cells(i, j) = str Then
            coll.Add ("Row :" & i & " Column : " & j)
        End If 
    Next i
Next j

Set GetAllRowsFromSheet = coll

End Function

Sub FindFor3()

Dim coll2 As Object

Set coll2 = GetAllRowsFromSheet(3)

Dim j As Integer

For j = 0 To coll2.Count - 1

Debug.Print "A match is found in " & coll2(j)

Next j

End Sub

Lookfor3 Ausgabe:

A match is found in Row :2 Column : 2
A match is found in Row :6 Column : 2
A match is found in Row :9 Column : 2
A match is found in Row :1 Column : 4
A match is found in Row :3 Column : 4
A match is found in Row :7 Column : 4