在VBA中检查列中是否存在值

Question

在VBA中检查列中是否存在值

excelvbamatching

46

我有一个超过500行的数字列。我需要使用VBA检查变量X是否与该列中的任何值匹配。

有人可以帮帮我吗？

- Trung Tran

8个回答

49

最简单的方法是使用Match函数。

If Not IsError(Application.Match(ValueToSearchFor, RangeToSearchIn, 0)) Then
    ' String is in range

- chris neilsen

3

在VB编辑器的自动完成中没有显示，但似乎可以使用。 - Urchin

为了提供一些背景，如果没有匹配，Match 函数会返回一个错误，否则它会返回一个双精度浮点数。 - OrigamiEye

使用 Application.WorksheetFunction.Match 可以提供自动完成的访问。 - Andy G

25

如果您想在不使用 VBA 的情况下完成此操作，您可以结合使用 IF、ISERROR 和 MATCH。

因此，如果所有的值都在列 A 中，将此公式输入列 B：

=IF(ISERROR(MATCH(12345,A:A,0)),"Not Found","Value found on row " & MATCH(12345,A:A,0))

这将查找值为 "12345" 的内容（也可以是单元格引用）。如果未找到该值，MATCH 将返回 "#N/A"，而 ISERROR 会捕获它。

如果您想使用VBA，则最快的方法是使用FOR循环：

Sub FindMatchingValue()
    Dim i as Integer, intValueToFind as integer
    intValueToFind = 12345
    For i = 1 to 500    ' Revise the 500 to include all of your values
        If Cells(i,1).Value = intValueToFind then 
            MsgBox("Found value on row " & i)
            Exit Sub
        End If
    Next i

    ' This MsgBox will only show if the loop completes with no success
    MsgBox("Value not found in the range!")  
End Sub

你可以在VBA中使用工作表函数，但它们很挑剔，有时会抛出毫无意义的错误。使用FOR循环比较稳妥。

- Jake Bathman

还有一件事 - 我该如何让它在特定的工作表中搜索？我当前的程序中有几个工作表，我需要它搜索名为“Codes”的工作表。谢谢。 - Trung Tran

5

使用范围的查找方法比循环遍历每个单元格要快得多。 - scott

9

FYI，你可以比使用匹配函数更简单地实现：=countif(A:A,12345)>0 如果找到该数字就会返回True，否则返回False。 - nutsch

1

@user1547174 要针对特定工作表执行此操作，请像这样引用单元格：Sheets("Codes").Cells(i,1).Value@scott 正确，但如果用户不检查许多值，可能更容易在范围上实现FOR循环。如果@user1547174想要使用此方法，请查看MSDN上的FIND方法：http://msdn.microsoft.com/en-us/library/office/ff839746.aspx - Jake Bathman

@nutsch 想得真好。同时，通过VBA，Application.WorksheetFunction.CountIf也非常有效。 - ashleedawg

9

试一下这个：

If Application.WorksheetFunction.CountIf(RangeToSearchIn, ValueToSearchFor) = 0 Then
Debug.Print "none"
End If

- user11078722

2

只是修改Scott的答案，使它成为一个函数：

Function FindFirstInRange(FindString As String, RngIn As Range, Optional UseCase As Boolean = True, Optional UseWhole As Boolean = True) As Variant

    Dim LookAtWhat As Integer

    If UseWhole Then LookAtWhat = xlWhole Else LookAtWhat = xlPart

    With RngIn
        Set FindFirstInRange = .Find(What:=FindString, _
                                     After:=.Cells(.Cells.Count), _
                                     LookIn:=xlValues, _
                                     LookAt:=LookAtWhat, _
                                     SearchOrder:=xlByRows, _
                                     SearchDirection:=xlNext, _
                                     MatchCase:=UseCase)

        If FindFirstInRange Is Nothing Then FindFirstInRange = False

    End With

End Function

这个函数会返回 FALSE 如果没有找到该值，如果找到了，则返回其范围。

您可以选择告诉它区分大小写和/或允许部分匹配单词。

我去掉了 TRIM，因为如果需要的话，您可以事先添加它。

一个例子：

MsgBox FindFirstInRange(StringToFind, Range("2:2"), TRUE, FALSE).Address

这是一个对第二行进行大小写敏感、部分单词搜索的功能，并显示带有地址的框。以下是相同的搜索，但它是不区分大小写的整个单词搜索：

MsgBox FindFirstInRange(StringToFind, Range("2:2")).Address

您可以轻松地根据自己的喜好调整此功能，或将其从Variant更改为布尔值，或者采用其他方法（如暴力循环或匹配）来加快速度。请注意，VBA的查找有时比其他方法慢，因此不要认为它是最快的，仅仅因为它是VBA本身的原生方法。它更复杂和灵活，这也可能使它并不总是高效的。而且它还有一些有趣的小问题需要注意，比如“对象变量或块变量未设置”error。

- sdanse

我喜欢这个概念，但当字符串未找到时，你的示例无法工作，例如 MsgBox FindFirstInRange(StringToFind, Range("2:2")).Address。它会抛出“对象必需”错误。 - JeffC

由于这种方法是唯一有希望的方法，我解决了JeffC提到的问题：如果输入的搜索字符串为空，则出现问题。我更改了函数，使返回值始终为布尔值。您可以在下面找到我的完整答案。 - MaKaNu

1

修复了@JeffC在@sdanse函数中提到的问题：

Function FindFirstInRange(FindString As String, RngIn As Range, Optional UseCase As Boolean = True, Optional UseWhole As Boolean = True) As Variant

    Dim LookAtWhat As Integer

    If UseWhole Then LookAtWhat = xlWhole Else LookAtWhat = xlPart

    With RngIn
        Set FindFirstInRange = .Find(What:=FindString, _
                                     After:=.Cells(.Cells.Count), _
                                     LookIn:=xlValues, _
                                     LookAt:=LookAtWhat, _
                                     SearchOrder:=xlByRows, _
                                     SearchDirection:=xlNext, _
                                     MatchCase:=UseCase)
        
        If FindFirstInRange Is Nothing Then
            FindFirstInRange = False
            Exit Function
        End If
        
        If IsEmpty(FindFirstInRange) Then
            FindFirstInRange = False
        Else
            FindFirstInRange = True
        End If
            
    End With

End Function

- MaKaNu

-1

=IF(COUNTIF($C$2:$C$500,A2)>0,"Exist","Not Exists")

- Hasan_Naser

2

此答案被标记为低质量，可能需要解释。以下是一些如何撰写好的答案？的指南。仅有代码的答案不被认为是好的答案，并且很可能会因为对学习者社区不够有用而被踩或删除。这只是对你来说很明显。请解释它的作用，以及它与现有答案的不同之处/优势（如果有）。来自审核。 - Trenton McKinney

-1

尝试添加WorksheetFunction：

If Not IsError(Application.WorksheetFunction.Match(ValueToSearchFor, RangeToSearchIn, 0)) Then
' String is in range

- Chris

1

使用WorksheetFunction会改变VBA处理无法找到值时出现的错误的方式。与返回错误值（然后可以通过IsError函数进行检查）不同，VBA会抛出需要错误处理或将停止执行的错误。为了测试一个值是否存在，你只需要一个True或False的结果，而不是可能生成运行时错误。 - Michael

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- scott · Accepted Answer

使用范围的find方法比手动循环遍历所有单元格要快。

下面是在VBA中使用find方法的示例

Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter a Search value")
If Trim(FindString) <> "" Then
    With Sheets("Sheet1").Range("A:A") 'searches all of column A
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True 'value found
        Else
            MsgBox "Nothing found" 'value not found
        End If
    End With
End If
End Sub