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
Do
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:

One thought on “Using Find and FindNext Functions in VBA

  1. Cordelia

    Thanks so much for the kind words. As you can tell, I have a love not just for poetry, but for the craft of poetry, whether in free verse or form. I thank dVerse Poets for giving me a chance to speak a little about that love, and all of the writers who conietbutrd here, who show what a little inspiration can do. To all of you – bravo!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *