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?

Answer 1

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