Using Find and FindNext Functions in VBA

How to use Find and FindNext functions in VBA to find data in an Excel worksheet quickly and easily. The major advantage of using Find and FindNext functions to search for data in an Excel worksheet is the speed. Using a ‘for next’ loop is also great to find data and perform actions on the data but the process is much slower.

Watch the video below before studying the VBA code:

In this video we discuss the Find method as it applies to a range. The Find method finds the data in a range and returns the first cell or range that contains the data.

If we have to search for multiple data of the same value, for example, we use the FindNext method. It finds the next range or cell that contains the same value as demonstrated in the video. It also returns a range object.

VBA code of Module1:

Sub usingFind()
Dim oWkSht As Worksheet
Dim lastrow As Long, i As Long, c As Long
Dim myCell As Range
Set oWkSht = Sheets(“Sheet1”)
lastrow = oWkSht.Range(“A” & Rows.Count).End(xlUp).Row
c = 9000
Set myCell = oWkSht.Range(“B1:B” & lastrow).Find(What:=c, LookIn:=xlValues)
If Not myCell Is Nothing Then
MsgBox “Value found in cell ” & myCell.Address
End If
Exit Sub
End Sub
VBA code for Module2:
Sub usingFindNext()
Dim c As Range
On Error Resume Next
With Worksheets(1).Range(“B1:B500”)
    Set c = .Find(10000, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
MsgBox “Value found in cell ” & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

Further Reading: