You don't need any VBA code for that! Use built-in formula:
=IF(ISNUMBER(FIND("cat",A2))>0,"yes","no")
A2
- is a reference to
descr
data.
If you would like to use VBA, you need to loop through the collection of cells:
Sub Test
Dim wsh As Worksheet
Dim i As Integer
Set wsh = Thisworkbook.Worksheets("Sheet1")
i = 2
Do While wsh.Range("A" & i)<>""
wsh.Range("B" & i) = Test4Cat(wsh.Range("A" & i))
i = i +1
Loop
End Sub
Function Test4Cat(ByVal sInput As String)
Test4Cat = IIf(InStr(1, LCase(sInput), "cat", vbTextCompare) > 0, "yes", "no")
End Function
Use the same logic with Regex ;)
[EDIT]
As concerns comments to the answer... you have to create custom function to validate if data meets your needs or no.
Function cfValidate(ByVal sDescription As String) As String
Dim vPatterns As Variant
Dim i as Integer
Dim retVal as String
retVal = "yes"
vPatterns = Array("pattern1","pattern2","pattern3")
For i=LBound(vPatterns) To UBound(vPatterns)
retVal = "no"
Next i
cfValidate = retVal
End Function
Note:
vPatterns
is a "collection" (array) of patterns to check if string is valid.
You can use above function as formula:
=cfValidate(A2)
[/EDIT]