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:
Dim oWkSht As Worksheet
Dim lastrow As Long, i As Long, c As Long
lastrow = oWkSht.Range(“A” & Rows.Count).End(xlUp).Row
MsgBox “Value found in cell ” & myCell.Address
Dim c As Range
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