Check if value exists in column in VBA

Question

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?

score 60 · Answer 1 · answered Sep 28 '12 at 15:37

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

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. — Jake Bathman, Sep 28 '12 at 17:23

score 49 · Answer 2 · answered Sep 28 '12 at 23:25

49

Simplest is to use Match

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

answered Sep 28 '12 at 23:25

chris neilsen

52,446
10
84
123

3

Doesn't show in the auto-complete in VB editor, but seems to work – Urchin Sep 21 '16 at 17:39
To provide some context, if there is no match the Match returns an error,, otherwise it returns a double – OrigamiEye May 15 '23 at 11:51
Using `Application.WorksheetFunction.Match` will provide access to auto-complete. – Andy G May 31 '23 at 12:22

score 25 · Accepted Answer · answered Sep 28 '12 at 14:52

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.

answered Sep 28 '12 at 14:52

Jake Bathman

1,268
3
18
25

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 Sep 28 '12 at 14:58
5

It is much faster to use the find method of a range rather than looping through every cell – scott Sep 28 '12 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 Sep 28 '12 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: http://msdn.microsoft.com/en-us/library/office/ff839746.aspx – Jake Bathman Sep 28 '12 at 16:52
@nutsch good thinking. Also works great with `Application.WorksheetFunction.CountIf` via VBA. – ashleedawg Sep 02 '18 at 14:06

score 9 · Answer 4 · answered Feb 18 '19 at 11:18

9

try this:

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

answered Feb 18 '19 at 11:18

user11078722

91
1
1

sdanse · Answer 5 · 2019-11-20T18:49:54.257

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.

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, Apr 12 '21 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, May 27 '21 at 14:07

score 1 · Answer 6 · answered May 27 '21 at 14:11

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

score -1 · Answer 7 · answered May 02 '18 at 22:19

-1

Try adding WorksheetFunction:

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

answered May 02 '18 at 22:19

Chris

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 Jan 29 '19 at 04:02

score -1 · Answer 8 · answered Sep 27 '21 at 06:41

-1

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

answered Sep 27 '21 at 06:41

Hasan_Naser

204
3
13

2

This answer was flagged as [Low Quality](https://stackoverflow.com/help/review-low-quality) and could benefit from an explanation. Here are some guidelines for [How do I write a good answer?](https://stackoverflow.com/help/how-to-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](https://stackoverflow.com/review/low-quality-posts/29929164) – Trenton McKinney Sep 27 '21 at 18:10

Check if value exists in column in VBA

8 Answers8

Linked

Related