how to do a vague look-up

Try the attached

Sub test()
    Dim strSearch4 As String, k
    Dim c As Range
    Dim hs As Long
    Dim i As Long, counter As Long
    Application.ScreenUpdating = False
    k = Worksheets.Count
    Sheets(3).[g1].CurrentRegion.Offset(1).Clear
    strSearch4 = Worksheets(3).TextBox2.Value
    For i = 10 To k
       For Each c In Sheets(i).Range("F1:F100")
        If c.Value Like "*" & strSearch4 & "[BC]" Then
            counter = 1
            c(, -2).Resize(, 4).Copy Sheets(3).Range("G" & Rows.Count).End(xlUp)(2)
        End If
      Next
    Next
    If counter = 0 Then
        MsgBox "no info found, check the input"
    End If
    Application.ScreenUpdating = True
End Sub