Try
Sub test()
Dim a, i As Long, myPtn As String
With Sheets("sheet2")
myPtn = Join(Application.Transpose(.Range("a2", .Range("a" & Rows.Count).End(xlUp)).Value), Chr(2))
End With
With Sheets("sheet1").Cells(1).CurrentRegion.Resize(, 2)
a = .Value
With CreateObject("VBScript.RegExp")
.Global = True: .IgnoreCase = True
.Pattern = "([$()^|\\\[\]+*?.-])"
myPtn = Replace(.Replace(myPtn, "\$1"), Chr(2), "|")
.Pattern = "\b(" & myPtn & ")\b"
For i = 2 To UBound(a, 1)
If .test(a(i, 1)) Then a(i, 2) = "Yes" Else a(i, 2) = "No"
Next
End With
.Value = a
End With
End Sub
Bookmarks