Find, FindNext and FindPrevious in Excel with VBA code

Find

In Excel Find method helps finding value in a specific range, sheet or workbook. in VBA (Visual Basic for Applications) Find method returns range object if no match found, it returns Nothing which can be used to validate response.

Syntax

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Parameters

1. What: it is mandatory parameter and variant type which requires a string that needs to be lookup or find.
2. After: its is an optional parameter and variant type where search needs to be performed after. It should be single cell range.
3. LookIn: its is an optional parameter and variant type where search needs to be looked into. Look in can be down within following areas using below constants derived from XlFindLookIn Enum:
   • xlFormulas: the find will take place within formula in each cell
   • xlValues: the find will take place within value in each cell
   • xlComments: the find will take place within comment in each cell
   • xlCommentsThreaded: the find will take place within comment thread
4. LookAt: its is an optional parameter and variant type which shows the lookup will happen within whole content or part of contents. LookAt can be done with the help of below constants derived from XlLookAt enum:
   • xlWhole: Looked at whole contents
   • xlPart: Looked at part of contents
5. SearchOrder: its is an optional parameter and variant type which determines search order in a range or sheet row-wise or column-wise. There are following constants can be mapped to achieve Search Order in a Find method derived from XlSearchOrder enum:
   1. xlByRows: search will happen row by row
   2. xlByColumns: search will happen column by column
6. SearchDirection: its is an optional parameter and variant type which determines search direction exposed by XlSearchDirection enum having following constants:
   • xlNext: move search forward by following Find
   • xlPrevious: perform search moving previous
7. MatchCase: its is an optional parameter and variant type which can be set True or False, if True then identical match will be performed else case sensitivity will be ignored.
8. MatchByte: its is an optional parameter and variant type works only dual byte language support is available in Excel. It can be set using True or False.
9. SearchFormat: refers to search format, its is an optional parameter and variant type in nature.

Data source

Consider following data source where we will perform Find operation following exact match and highlight the result by setting the cell background color to Blue and show row and column number with value in a message box:

Code example

Public Sub FindExample()
    'Capture selection and bind range object
    Dim oRange As Range
    Set oRange = Selection
    
    'Object to hold find result
    Dim oFindResult As Range
    
    'Perform find operation
    Set oFindResult = oRange.Find("Bus", LookIn:=xlValues)
    
    'Validate result
    If Not oFindResult Is Nothing Then
        oFindResult.Interior.Color = vbBlue
        MsgBox "We found " & oFindResult.Value & " value at Row Number " & oFindResult.Row & " and at column number " & oFindResult.Column
    Else
        MsgBox "No match found"
    End If
End Sub

Output

FindNext

Find will be continue to the next search when Find Next method is called in a range or sheet. Consider following data where we will find “Bus” which is repeating in the range and highlight all with Red color.

Data source

Code example

Public Sub FindNextExample()
    'Capture selection and bind range object
    Dim oRange As Range
    Set oRange = Selection
    
    'Object to hold find result
    Dim oFindResult As Range
    
    'Perform find operation
    Set oFindResult = oRange.Find("Bus", LookIn:=xlValues)
    
    'Validate result
    If Not oFindResult Is Nothing Then
        oFindResult.Interior.Color = vbGreen
        Dim CurrntRowNumber As Integer
        CurrntRowNumber = oFindResult.Row
        
        'Loop through each find
        Do
            'Perform Next find
            Set oFindResult = oRange.FindNext(oFindResult)
            
            'Validate next result
            If Not oFindResult Is Nothing Then
                oFindResult.Interior.Color = vbGreen
            Else
                Exit Sub
            End If
            
        Loop While oFindResult.Row <> CurrntRowNumber
    Else
        MsgBox "No match found"
    End If
End Sub

Output

FindPrevious

Search begin from Find would be continue and find the cell that matches same conditions provided to find and return Range. FindPrevious method works with Find and FindNext method.

Data source

Code example

Public Sub FindPreviousExample()
    'Capture selection and bind range object
    Dim oRange As Range
    Set oRange = Selection
    
    'Object to hold find result
    Dim oFindResult As Range
    
    'Perform find operation
    Set oFindResult = oRange.Find("Bus", LookIn:=xlValues)
    
    If Not oFindResult Is Nothing Then
        
        Debug.Print "First match found at " & oFindResult.Address
        
        'Find next
        Set oFindResult = oRange.FindNext(after:=oFindResult)
        
        If Not oFindResult Is Nothing Then
            Debug.Print "Second match found at " & oFindResult.Address
            
            'Find previous
            Set oFindResult = oRange.FindPrevious(after:=oFindResult)
            
            If Not oFindResult Is Nothing Then
                Debug.Print "Previous match found at " & oFindResult.Address
            End If
        End If
    End If
    
    'Cleanup
    Set oRange = Nothing
    Set oFindResult = Nothing
End Sub

Output

First match found at $A$4
Second match found at $A$6
Previous match found at $A$4

Please leave your comments or queries under comment section also please do subscribe to our blogs to keep your self upto date.

Share on Facebook

Tweet