Excel 2010 Macro Search for Text (String)

 

If you ever needed to find out which cells contain a specific string of text, this function can search strings in a range, looking for specified text. The result identifies which cells contain the text.

ContainsText(Rng,Text)

The arguments are:

 

Function example:

Function ContainsText(Rng As Range, Text As String) As String

Dim T As String

Dim myCell As Range

For Each myCell In Rng ‘look in each cell

    If InStr(myCell.Text, Text) > 0 Then ‘look in the string for the text

        If Len(T) = 0 Then ‘if the text is found, add the address to my result

            T = myCell.Address(False, False)

        Else

            T = T & “,” &myCell.Address(False, False)

        End If

    End If

Next myCell

ContainsText = T

End Function