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