47

I have a column of numbers of over 500 rows. I need to use VBA to check if variable X matches any of the values in the column.

Can someone please help me?

8 Answers 8

62

The find method of a range is faster than using a for loop to loop through all the cells manually.

here is an example of using the find method in vba

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
1
  • 3
    Thanks for doing this, Scott. It'll be more robust for non-numeric values than the FOR loop. @user1547174 you can use the variable Rng to get information about the location of the match, specifically calling Rng.Address which returns the cell location as a string. Commented Sep 28, 2012 at 17:23
49

Simplest is to use Match

If Not IsError(Application.Match(ValueToSearchFor, RangeToSearchIn, 0)) Then
    ' String is in range
3
  • 3
    Doesn't show in the auto-complete in VB editor, but seems to work
    – Urchin
    Commented Sep 21, 2016 at 17:39
  • To provide some context, if there is no match the Match returns an error,, otherwise it returns a double
    – OrigamiEye
    Commented May 15, 2023 at 11:51
  • Using Application.WorksheetFunction.Match will provide access to auto-complete.
    – Andy G
    Commented May 31, 2023 at 12:22
25

If you want to do this without VBA, you can use a combination of IF, ISERROR, and MATCH.

So if all values are in column A, enter this formula in column B:

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

This will look for the value "12345" (which can also be a cell reference). If the value isn't found, MATCH returns "#N/A" and ISERROR tries to catch that.

If you want to use VBA, the quickest way is to use a FOR loop:

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

You can use Worksheet Functions in VBA, but they're picky and sometimes throw nonsensical errors. The FOR loop is pretty foolproof.

5
  • One more thing - How can I ask it to search in a specific worksheet? I have several worksheets in my current program and I need it to search a sheet called "Codes". Thank you.
    – Trung Tran
    Commented Sep 28, 2012 at 14:58
  • 5
    It is much faster to use the find method of a range rather than looping through every cell
    – scott
    Commented Sep 28, 2012 at 15:33
  • 9
    FYI, you can do much easier than the match function: =countif(A:A,12345)>0 will return True if the number is found, false if it isn't.
    – nutsch
    Commented Sep 28, 2012 at 15:54
  • 1
    @user1547174 To do this for a specific sheet, reference the cells like this: Sheets("Codes").Cells(i,1).Value @scott true, but if the user isn't checking many values it might be easier to implement a FOR loop on the range. If @user1547174 wants to use this method, check out the FIND method on MSDN: msdn.microsoft.com/en-us/library/office/ff839746.aspx Commented Sep 28, 2012 at 16:52
  • @nutsch good thinking. Also works great with Application.WorksheetFunction.CountIf via VBA.
    – ashleedawg
    Commented Sep 2, 2018 at 14:06
10

try this:

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

Just to modify scott's answer to make it a function:

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

This returns FALSE if the value isn't found, and if it's found, it returns the range.

You can optionally tell it to be case-sensitive, and/or to allow partial-word matches.

I took out the TRIM because you can add that beforehand if you want to.

An example:

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

That does a case-sensitive, partial-word search on the 2nd row and displays a box with the address. The following is the same search, but a whole-word search that is not case-sensitive:

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

You can easily tweak this function to your liking or change it from a Variant to to a boolean, or whatever, to speed it up a little.

Do note that VBA's Find is sometimes slower than other methods like brute-force looping or Match, so don't assume that it's the fastest just because it's native to VBA. It's more complicated and flexible, which also can make it not always as efficient. And it has some funny quirks to look out for, like the "Object variable or with block variable not set" error.

2
  • I like the concept of this but your examples won't work when the string isn't found, e.g. MsgBox FindFirstInRange(StringToFind, Range("2:2")).Address. It will throw "Object Required".
    – JeffC
    Commented Apr 12, 2021 at 20:20
  • Since this approach was the only one promising I fixed the problem mentioned by JeffC: The Problem is the Situation, if the Search String entered is empty. I changed the function so the return value is always a Boolean. You find my complete awnser somewhere below.
    – MaKaNu
    Commented May 27, 2021 at 14:07
1

Fixed Problem mentioned by @JeffC in the function from @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
1
  • Not working for me, "object required" error is shown
    – bahamut100
    Commented Feb 6 at 10:39
-1

Try adding WorksheetFunction:

If Not IsError(Application.WorksheetFunction.Match(ValueToSearchFor, RangeToSearchIn, 0)) Then
' String is in range
1
  • 1
    Using WorksheetFunction changes how VBA handles the error that arises if the value can't be found. Rather than returning an error value (which can then be checked by the IsError function), VBA throws an error that requires error handling or will halt execution. For testing whether a value exists, you just want a True or False result, not to potentially generate a run-time error.
    – Michael
    Commented Jan 29, 2019 at 4:02
-1
=IF(COUNTIF($C$2:$C$500,A2)>0,"Exist","Not Exists")
1
  • 2
    This answer was flagged as Low Quality and could benefit from an explanation. Here are some guidelines for How do I write a good answer?. Code only answers are not considered good answers and are likely to be downvoted and/or deleted because they are less useful to a community of learners. It's only obvious to you. Explain what it does, and how it's different / better than existing answers (if there are any). From Review Commented Sep 27, 2021 at 18:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.