String Search

[ColinKJ]

Hi All,

If I have a text string in a variable, can anyone give me a VBA line to see if that sting already exists in a particular column of data.

Regards

ColinKJ

 

[VoG]

One way

Dim IsThere As Boolean
IsThere = IsNumeric(Application.Match(txt, Columns("A"), 0))
MsgBox IsThere

 

[Domski]

Or using the Find Method:

With Worksheets("Sheet1").Range("A1:A500")
    Set c = .Find(myVariable, LookIn:=xlValues)
    If Not c Is Nothing Then
        MsgBox "Found at: " & c.Address
    Else
        MsgBox "Not Found"
    End If
End With

Dom

 

[Kaps_mr2]

Try :-

Public Function Found_Text (Range_to_search As range, Text_to_Find as String) As Boolean
Found_text = False
 
If (Application.worksheetfunction.countif(Range_to_search,Text_to_find)>0) Then 
Found_Text = True
End If
 
End Function

Thks Kaps

 

[ColinKJ]

What an execelent response.

Thanks guys

ColinKJ