Finding Values with Excel VBA

Commands from the Find & Select list on the Home tab in the Editing group allow you to quickly find and replace cell content according to specified criteria or simply perform a search. With VBA, you can also specify criteria for searching data within a specific range, perform replacements, etc. Let’s look at some examples.

Finding a Value in a Range
The Find method of the Range object searches for specified information within a given range and returns a reference to the first cell where the value is found. If the data is not found, the method returns Nothing.

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

• What — required parameter specifying the data to search for.
• After — optional parameter indicating the cell after which to start the search.
• LookIn — optional parameter specifying where to search. Acceptable XlFindLookIn constants: xlComments, xlFormulas, xlValues.
• LookAt — optional parameter specifying how to search. Acceptable XlLookAt constants: xlWhole, xlPart.
• SearchOrder — optional parameter specifying the order of scanning the range. Acceptable XlSearchOrder constants: xlByRows, xlByColumns.
• SearchDirection — optional parameter specifying the search direction. Acceptable XlSearchDirection constants: xlNext, xlPrevious.
• MatchCase — optional parameter indicating whether to consider case.
• MatchByte — optional parameter, rarely used.
• SearchFormat — optional parameter specifying the search format.

For example, the following code  searches for the value 17 in the range A1:A10. If found, a message box displays the address of the first found cell.

Finding a Value

Sub Find1()
    Dim rng As Range
    Set rng = Range("A1:A10").Find(What:=17, LookIn:=xlValues)
    If Not (rng Is Nothing) Then
        MsgBox rng.Address
    Else
        MsgBox "Value not found"
    End If
End Sub

The code searches for the substring « BHV » case-insensitively in the range A1:A20. If found, a message box displays the Value of the found cell.

Finding a Substring Case-Insensitive

Sub DemoFindNoMatchCase()
    Dim rng As Range
    Set rng = Range("A1:A20").Find(What:="BHV", LookIn:=xlValues, _
        LookAt:=xlPart, MatchCase:=False)
    If Not (rng Is Nothing) Then
        MsgBox rng.Value
    Else
        MsgBox "No matching value found"
    End If
End Sub