Find item in Column (input with Textbox) and copy all matches to another sheet.

[Bruno74]

Hello, i am a new user. From Portugal (sorry mistakes).
I have a Userform with a Textbox and a button. Textbox to input the item and search button to find.

I want:
To search for an item in Column "C" of Sheet1 (input on textbox),
It will find more than 5 when i click the search button.
I need to copy those entire rows to Sheet2, all 8 columns to Sheet2 that contains the item.


This is my code. It works for item "1" (only item in order) but when i search for item "2" and so on, it only copies one row to sheet2 but exists more than 5 rows.
Tks for the help.

Private Sub CommandButton1_Click()
 


Dim RowNo As Long, ColNo As Integer, NRow As Long, Sheet1 As Worksheet, Sheet2 As Worksheet


Dim R As Range, vResult As Variant, lRowEnd As Long


Dim Str As String
 


Str = TextBox1


If Str = "" Then Exit Sub


Set Sheet1 = Sheets("Sheet1")


Set Sheet2 = Sheets("Sheet2")


Sheet2.Cells.Clear


 
NRow = 0


 
'** Test if more than 1 entry for requd string **


If WorksheetFunction.CountIf(Sheet1.Columns("C"), Str) > 1 Then


    lRowEnd = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row


    RowNo = 2


    On Error Resume Next


    vResult = "*"


    vResult = WorksheetFunction.Match(Str, Sheet1.Range("C" & RowNo, "C" & lRowEnd), 0)


    Do While IsNumeric(vResult)


        RowNo = RowNo + vResult - 1


        NRow = NRow + 1


       
        Sheet2.Cells(NRow, 1) = Sheet1.Cells(RowNo, 1)


        Sheet2.Cells(NRow, 2) = Sheet1.Cells(RowNo, 2)


        Sheet2.Cells(NRow, 3) = Sheet1.Cells(RowNo, 3)


        Sheet2.Cells(NRow, 4) = Sheet1.Cells(RowNo, 4)


        Sheet2.Cells(NRow, 5) = Sheet1.Cells(RowNo, 5)


        Sheet2.Cells(NRow, 6) = Sheet1.Cells(RowNo, 6)


        Sheet2.Cells(NRow, 7) = Sheet1.Cells(RowNo, 7)


        Sheet2.Cells(NRow, 8) = Sheet1.Cells(RowNo, 8)


       
        RowNo = RowNo + Val(vResult)


        vResult = "*"


        vResult = WorksheetFunction.Match(Str, Sheet1.Range("C" & RowNo, "C" & lRowEnd), 0)


    Loop


    On Error GoTo 0


End If


 
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub CommandButton1_Click()
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   Dim Fnd As Range
   Dim i As Long, Qty As Long, Nrow As Long
   Dim Str As String
   
   Set Ws1 = Sheets("Sheet1")
   Set Ws1 = Sheets("Sheet2")
   Set Fnd = Ws1.Range("C" & Rows.Count)
   Str = TextBox1.Value
   
   If Str = "" Then Exit Sub
   Ws2.Cells.Clear
   
   Qty = WorksheetFunction.CountIf(Ws1.Columns("C"), Str)
   
   For i = 1 To Qty
      Nrow = Nrow + 1
      Set Fnd = Ws1.Range("C:C").Find(Str, Fnd, , xlWhole, xlByRows, xlNext, False, , False)
      Ws2.Cells(Nrow, 1).Resize(, 8).Value = Fnd.Resize(, 8).Value
   Next i
End Sub

 

[Bruno74]

Tks Fluff.

I´ve copied the entire code and replace it, but it gives me an error

error91
Object variable or With block variable not set

 

[Fluff]

Small typo, & another mistake, try

Private Sub CommandButton1_Click()
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   Dim Fnd As Range
   Dim i As Long, Qty As Long, Nrow As Long
   Dim Str As String
   
   Set Ws1 = Sheets("Sheet1")
   Set [COLOR=#ff0000]Ws2 [/COLOR]= Sheets("Sheet2")
   Set Fnd = Ws1.Range("C" & Rows.Count)
   Str = TextBox1.Value
   
   If Str = "" Then Exit Sub
   Ws2.Cells.Clear
   
   Qty = WorksheetFunction.CountIf(Ws1.Columns("C"), Str)
   
   For i = 1 To Qty
      Nrow = Nrow + 1
      Set Fnd = Ws1.Range("C:C").Find(Str, Fnd, , xlWhole, xlByRows, xlNext, False, , False)
      Ws2.Cells(Nrow, 1).Resize(, 8).Value = Fnd[COLOR=#ff0000].Offset(, -2)[/COLOR].Resize(, 8).Value
   Next i
End Sub

 

[Bruno74]

Sorry Fluff, didn't notice that.

You are amazing!!! And just like that...it works!!!

Tks a lot!!!!!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Bruno74]

One more thing, if possible.

Using that new code, how can i make the copied items (the ones that goes to Sheet2), to start in the second row ( first row is for headers)

Tks again

 

[Fluff]

Add Nrow=1 just before the For i= line

 

[Bruno74]

Just perfect!!!

Thank you very much!

 

[Fluff]

My pleasure