[excel] Check if value exists in column in VBA

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?

try this:

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

Try adding WorksheetFunction:

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

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.

Simplest is to use Match

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

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