使用 Excel VBA 在列中查詢值
Glen Alfaro
2023年1月30日
2022年5月18日
在 Excel 中建立自動化工具需要幾個字串匹配函式,如 Instr()
、CStr()
、Split()
等。這些函式在字串中查詢子字串。但是,當通過 column/s 處理字串/值時,你不能使用這些函式,因為它們是單字串函式。
本文將演示在列中查詢字串或值的三種技術。return
值是目標字串所在的行號。
字串搜尋技術:
- 使用
Find()
函式 - 使用
匹配
功能 - 使用迴圈
對於整篇文章,此虛擬表將作為參考表。
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)
引數:
Range |
必需的。Range 物件在哪裡檢視值或字串。 |
What |
必需的。要搜尋的字串。 |
After |
可選的。你希望在其後開始搜尋的單元格。 |
LookIn |
可選的。它可以是 xlFormulas 、xlValues 。 |
LookAt |
可選的。它可以是 xlPart 或 xlWhole 。 |
SearchOrder |
可選的。它可以是 xlByRows 或 xlByColumns 。 |
SearchDirection |
可選的。它可以是 xlNext 或 xlPrevious 。 |
MatchCase |
可選的。如果搜尋區分大小寫,則為 True ,否則為 False 。 |
下面的程式碼塊將演示使用 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])
引數:
[StringtoSearch] |
必需的。要查詢的字串 |
[RangetoSearchIn] |
必需的。Range 物件在哪裡檢視值或字串 |
[MatchType] |
可選的。Excel 匹配型別。詳見備註 |
評論:
對於 [MatchType]
引數,以下是值:
1 | 找到小於或等於 StringtoSearch 的最大值 |
0 | 找到準確的 StringtoSearch |
-1 | finds the least value that is greater than or equal to StringtoSearch |
下面的程式碼塊將演示使用 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