使用 Excel VBA 在列中查找值
Glen Alfaro
2023年1月30日
2022年5月18日
-
在 VBA 中使用
Find()
函数在列中查找字符串/值 -
在 VBA 中使用
Match()
函数在列中查找字符串/值 - 在 VBA 中使用循环查找列中的字符串/值
- 在 VBA 中使用循环查找多列中的字符串
在 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