Finding Text in a Named Range

[tlawson1]

Hi All,

Using Excel 2010. I have a variable named sName which is set to a string value of "Car"

Can anyone give me some pointers on how to check a Named Range to see if any of the cells contain the string "Car". This check needs to work for Upper and Lower case. i.e either "Car" or "car" would find a match.

Many thanks

 

[shg]

One way:

    Dim sName       As String
    Dim rFind       As Range
 
    sName = "car"
 
    Set rFind = Range("myRange").Find(What:=sName, _
                                      LookIn:=xlValues, _
                                      LookAt:=xlPart, _
                                      MatchCase:=False, _
                                      MatchByte:=False)
    If rFind Is Nothing Then
        MsgBox "Not Found"
    Else
        Application.Goto rFind
    End If

 

[tlawson1]

Many Thanks I spent ages trying to work that out

 

[shg]

You're welcome.

The arguments are well explained in Help.