January 15, 2016

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:

6 thoughts on “Using Find and FindNext Functions in VBA

  1. 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!

  2. sir i am big fan of you. sir i want to make a user form that can make search only by first name or last name and select that cell where is the data if that data is not my Required data then search next and so on when i search my Required data. and there is one button more for previous search if i click that button then select the previous searched cell and so on. sir is this possible. if this not possible then please massage me because i am looking for that.
    [email protected]

  3. Hi can you show us how to write the VBA to find column B valve > than for example > than 10000

    Thank you

  4. Hello
    i am working on a form and i have followed your videos to make progress. That i am very grateful. I have a question . I need to create a user form to search a row for an input key word and display it on my form. so far i have followed your videos and it has been great. but i need to write a vba for a next button so it can display the next result and may be the previous result. All i ahve seen you do is ind a data and go to the next line but i need the next find. below is code to put the value in the box but i need VBA to move to next found result.
    thank you so much
    Private Sub CMD_SEARCH_Click()
    ‘**Define variables**
    Dim TotRows As Long, i As Long, c As Long
    Dim ANSWER As VbMsgBoxResult
    TotRows = Worksheets(“SHEET1”).Range(“F1”).CurrentRegion.Rows.Count
    ‘**Define empty Search**
    If TextBox_SEARCH.Text = “” Then
    End If
    ‘**Now starting with the row 3**
    For i = 3 To TotRows
    ‘**Define condition if no search found**
    If Trim(Sheet1.Cells(i, 6)) Trim(TextBox_SEARCH.Text) And i = TotRows Then
    End If
    ‘**Define value search criteria**
    If Trim(Sheet1.Cells(i, 6)) = Trim(TextBox_SEARCH.Text) Then
    TextBox_NAME.Text = Sheet1.Cells(i, 1)
    TextBox_BEMS.Value = Sheet1.Cells(i, 2)
    TextBox_BN.Value = Sheet1.Cells(i, 3)
    Exit For

    End If
    Next i

  5. FindNext example great and seen on many yt vids – however – finding a static value such as 9,000 is simple. How would you find
    all instances of say, the word “wood” in the middle of a cell with text nd copy a section of that row, A:H to another sheet, from a userform input using a string variable(not a range var) ? I can find no solution on how to do this, as all solutions use exact values or numbers in cells and not a value in the middle of text in a cell.

Comments are closed.