使用 Excel VBA 在列中查找值

1. 在 VBA 中使用 Find() 函数在列中查找字符串/值
2. 在 VBA 中使用 Match() 函数在列中查找字符串/值
3. 在 VBA 中使用循环查找列中的字符串/值
4. 在 VBA 中使用循环查找多列中的字符串

在 Excel 中创建自动化工具需要几个字符串匹配函数，如 Instr()、CStr()、Split() 等。这些函数在字符串中查找子字符串。但是，当通过 column/s 处理字符串/值时，你不能使用这些函数，因为它们是单字符串函数。

本文将演示在列中查找字符串或值的三种技术。return 值是目标字符串所在的行号。

字符串搜索技术：

1. 使用 Find() 函数
2. 使用匹配功能
3. 使用循环

对于整篇文章，此虚拟表将作为参考表。

Sheet1 值：

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

在 VBA 中使用 Find() 函数在列中查找字符串/值

语法：

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

参数：

下面的代码块将演示使用 Find() 函数返回要搜索的字符串行。

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 输出：

Found at row: 3

MelonSearch 输出：

Not in range

在 VBA 中使用 Match() 函数在列中查找字符串/值

Find() 和 Match() 函数之间的主要区别在于，前者返回找到字符串的 Range 对象，而后者返回字符串匹配的位置。

语法：

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

参数：

评论：

对于 [MatchType] 参数，以下是值：

下面的代码块将演示使用 Match() 函数返回要搜索的字符串的行。

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 输出：

A match is located at row 3

LookForZero 输出：

A match is located at row Not Found

在 VBA 中使用循环查找列中的字符串/值

与 Find() 和 Match() 函数不同，使用循环允许用户执行代码。循环的一个优点是我们可以返回 Range 中的所有匹配项。但是，在处理大数据时不建议使用循环，因为它会在指定限制内​​迭代每个单元格。

下面的代码块将演示利用循环返回要搜索的字符串行。

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 输出：

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

在 VBA 中使用循环查找多列中的字符串

上面的三个代码块旨在仅查看一列。很少需要在多个列甚至整个工作表中查找字符串匹配项。但是，如果你需要，下面的代码块将为你提供帮助。

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 输出：

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