使用 Excel VBA 在列中查找值 |
您所在的位置:网站首页 › 返回某列的值 › 使用 Excel VBA 在列中查找值 |
在 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 SubBananaSearch 输出: Found at row: 3MelonSearch 输出: 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 SubLookForSix 输出: A match is located at row 3LookForZero 输出: 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 SubFindforMexico 输出: 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 SubLookfor3 输出: 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 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |