CODEWITHSUNDEEP
excelvba
user13316884
user13316884

Reputation: 47

Expression for 'Find' command

I want to use 'Find' command to search for a word one cell at a time instead of a range of cells at a time. I tried using 'For Each' command as shown below but it does not work. Is it possible to search for word using other expressions such as e.g Set rgFound = activecell.Find("John") or Set rgFound = cells(1,1).Find("John")) apart from using 'range'?

Sub tester()
    Dim rgFound As Range
    Set rng = Columns(1)
    Set rng1 = rng.SpecialCells(xlVisible)

    For Each cell In rng1
        Set rgFound = cell.Find("John")
        If rgFound Is Nothing Then
            MsgBox "Name was not found."
        Else
            MsgBox "Name found in :" & rgFound.Address
        End If
    Next
End Sub

Upvotes: 1

Views: 46

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

To search in the value of one single cell you must use the InStr function

If InStr(1, Range("A1").Value, "John", vbTextCompare) > 0 Then
    'John was found
Else
    'John was not found
End If

InStr returns the first position where John was found in the value of cell A1, so if it returns a number > 0 then it was found otherwise not.

Alternatively you can use the Like operator:

If Range("A1").Value Like "*John*" Then
    'John was found
Else
    'John was not found
End If

Note the surounding placesholders (* asterisks) to ensure the like operator recognizes John in "any string that conains John in the begining middele or end of the string"

Upvotes: 2

Related Questions