how to do a vague look-up

Hi,
I tried to write the attached the code for a vague look-up and then copy the relevant info to the blank space, but it pop-up the error showed "compile error: Expected Expression"
dose someone can help to see which line I did not write properly？
thks.
Capture.JPG

Public Sub Name()
Dim strSearch4 As String, c As Range, i As Long, counter As Long
Application.ScreenUpdating = False
counter = 0
strSearch4 = "*" & Worksheets(3).TextBox2.Value & "*"
For i = 10 To Sheets.Count
    For Each c In Sheets(i).Range("C1:C100")
        If c.Value Like strSearch4 Then
            counter = counter + 1
            c.Resize(, 3).Copy Sheets(3).Range("G" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next
Next
If counter = 0 Then
    MsgBox "no info found, check the input"
End If
Application.ScreenUpdating = True
End Sub

Kind regards
Leo

Hi Leo,
I tried ur code, but it displayed compile error again.

It runs in file you posted


Kind regards
Leo

Hi Leo,
thks for the help.
I ran it again and it is Ok.
But I tried to look up the model# and copy the entire row and I changed some of codes.
unfortunately, when run the code, it showed "object variable or with block variable not set"
does it mean if there sth. I dun define properly?

Hi xlnitwit,
thks for reminder

Cod for search with textbox2

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

Cheers
Leo

Hi Leo,
Thks for helping me to solve the problem again.
however, for the model searching, I do not want to come out too many results.
e.g, for 8157, since I knew there is 8157A, 8157B & 8157C, I just wanna these results but not 81577A.
so I hope when I input 8157X, that means the only one I am not sure is in "X" part, and after searching I can get the 8157A/B/C result but not the items like 81577A
thks.

Originally Posted by VBAidot

e.g, for 8157, since I knew there is 8157A, 8157B & 8157C, I just wanna these results but not 81577A.
so I hope when I input 8157X, that means the only one I am not sure is in "X" part, and after searching I can get the 8157A/B/C result but not the items like 81577A

 Public 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
    strSearch4 = Worksheets(3).TextBox2.Value
    For i = 10 To k
       For Each c In Sheets(i).Range("C1:C100")
        If c.Value Like "*" & strSearch4 & "[BC]" Then
            counter = 1
            c.EntireRow.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

HI Jindon,
thks for replying.
But ,since I need to search the model#, so I changed the range from ("C1:C100") to (F1:F100) and input the 8157 into textbox2 and run the code, it showed "compile error"

Hi Jindon,
I tried ur code and changed the range from (C1:C100) to (F1:F100) since I will search the model#, it also appeared "compile error"
could you pls help to check ?
thks.

Only 8157 in textbox2

Public Sub Text_Box_2()
Dim strSearch4 As String, c As Range, i As Long, counter As Long, hs As Long
Application.ScreenUpdating = False
Sheets(3).Range("G:J").ClearContents
counter = 0
strSearch4 = "*" & Worksheets(3).TextBox2.Value & "?"
For i = 10 To Sheets.Count
    For Each c In Sheets(i).Range("F1:F100")
        If c.Value Like strSearch4 Then
            counter = counter + 1
           c.Offset(, -3).Resize(, 4).Copy Range("G" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next
Next
If counter = 0 Then
    MsgBox "no info found, check the input"
End If
Application.ScreenUpdating = True
End Sub

Cheers
Leo

Hi Leo,
I input the 8157 in textbox2 & run the code, it showed the "compile error"

Hi Leo,
I input the 8157 in textbox2 & run the code, it showed the "compile error"[/QUOTE]

Hi Leo,
I input 8157 in textbox2 & run the code, it showed "compile error"

I dont have the error, so i have to compere the files

Kind regards
Leo

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

Code runs by exit textbox

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

Cheers
Leo