Check If Value Exists In Column In VBA

Answer :

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 

Simplest is to use Match

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

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.