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?

Forgiveness

Answer 1

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.

Kruller

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. – Forgiveness

It is much faster to use the find method of a range rather than looping through every cell – Thermostatics

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. – Trinitrotoluene

@user1547174 To do this for a specific sheet, reference the cells like this: Sheets("Codes").Cells(i,1).Value @Thermostatics 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 – Kruller

@Trinitrotoluene good thinking. Also works great with Application.WorksheetFunction.CountIf via VBA. – Afloat

Answer 2

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

Thermostatics

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. – Kruller

Answer 3

Simplest is to use Match

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

Kerouac

Doesn't show in the auto-complete in VB editor, but seems to work – Tenebrae

To provide some context, if there is no match the Match returns an error,, otherwise it returns a double – Ambit

Using Application.WorksheetFunction.Match will provide access to auto-complete. – Olivine

Answer 4

try this:

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

Prize

Answer 5

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.

Lipocaic

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". – Position

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. – Pham

Answer 6

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

Pham

Not working for me, "object required" error is shown – Mascarenas

Answer 7

Try adding WorksheetFunction:

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

Dionysius

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. – Elastance

Answer 8

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

Hopple

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 – Polity