Search range, find any cell with partial match, select entire row then copy and paste

Hi everyone,

Just recently joined this form and just started coding for vba about 2 weeks ago or so. My issue i am having is that i would like to search a sheet range ("A2:P1100") and any cell that contains a partial match (e.g. "the" in "weather") i would like that cells entire row (in this case i have information in every cell from A:P) to be selected and pasted to "sheet2".

Once it has been pasted to another sheet I then want to specify it to populate a listbox that will dynamically change based on the number of rows i have filled in "sheet2"

The listbox is no problem, the issue i am having is that I cannot seem to find a simple way to select the entire cells row based on the cell found. Below I have posted the code i am using and works perfectly except for the fact that It only takes the value of the cell; where i want it to find that cell, select the entire row, then take the value of every cell in that row. Any help or input would be greatly appreciated.

Thank you in advance for everyone's input/help.

Private Sub CommandButton1_Click()


' activates Mastersheet where the data being searched resides and specifies the range to search in that sheet
Worksheets("Mastersheet").Select
With Range("A2:P1100")


' establishes what I am looking for (e.g. the value entered into textbox9) and returns any cells address that finds a full/parital match
Set c = .Find(what:=TextBox9.Text, LookIn:=xlValues, lookat:=xlPart)
    If Not c Is Nothing Then
        FirstAddress = c.Address
              
' takes the cells value and adds it to my listbox
Do
    ListBox1.AddItem c.Value
    Set c = .FindNext(c)
        
' loops until all cells found that contain a full/partial match
        Loop While Not c Is Nothing And c.Address <> FirstAddress
    End If
End With


End Sub

Re: Search range, find any cell with partial match, select entire row then copy and p

Well I started from scratch because I got tired of messing around with other people code for my purposes and what I came up with is listed below. Everything works perfectly except that I cannot seem to get it to loop through. The loop i have setup now stops at the first cell that matches my search. I am not sure how to properly word the loop so that it loops through every cell and when it gets back to the first cell that matches the criteria (say "B2") I want it to exit the loop so it doesnt go back through the exact same cells twice

Thanks again for all your help guys and girls,

Mike

Private Sub CommandButton1_Click()




Worksheets("Mastersheet").Select
With Range("A2:P1100")
Dim crange As Range
Dim cfound As Range
Dim lastRow As Long








Set crange = Cells.Find(what:=TextBox9.Text, after:=Range("A1"), LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext)
    For Each cfound In crange
        If cfound = crange Then
            'MsgBox ("found")
        
         
    Do
        cfound.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        FirstAddress = cfound.Address
        ListBox1.ColumnCount = 15
        ListBox1.ColumnWidths = "6 cm; 5cm; 2cm; 3cm; 4 cm; 3 cm; 3.5 cm; 4.5cm; 3 cm; 4 cm; 3.5 cm; 0 cm; 7.5 cm, 4 cm, 4cm  "
        lastRow = Worksheets("Sheet2").Cells(Sheet2.Rows.Count, 2).End(xlUp).Row
                Me.ListBox1.List = Worksheets("Sheet2").Range("B1:B" & lastRow, "B1:O13").Value
                
        Set cfound = .FindNext(cfound)
        Loop While Not cfound Is Nothing And crange.Address <> FirstAddress
            
    
    
        Else
            Exit Sub
    End If
Next cfound
End With






End Sub

Re: Search range, find any cell with partial match, select entire row then copy and p

Thanks for the cleanup I appreciate it! I'm still learning as I go so my code isn't the best nor is it the cleanest haha. As for the "for each" declaration no that does not need to be said that way. I wrote it that way because it was just what I thought would work at the moment. If you have a cleaner solution it would be greatly appreciate. In a nut shell all I want this code to do is:

1) search the entire worksheet
2) find any cells that contain a match or partial match (if you know of a way to exclude cells that only match 1 or 2 letters/number that would be great and restrict it more towards a whole rather than partial match)
3) once the cell with a match is found, select that entire row
4) either directly add that row to a listbox and continue searching for the next cell, or copy and paste that row to a new sheet (like I've been doing) and then specify the dynamic range to add to the listbox

again, thank you, and everyone else for your/their input it is greatly appreciated!

Re: Search range, find any cell with partial match, select entire row then copy and p

You are the man stephenR!

Thank you for your help everything works exactly the way I wanted it to. If you wouldnt mind could you explain to me what you saw I did wrong and explain why you changed the location of certain lines? If so that would be wonderful, if not then i fully understand.

One last question, if someone were to search "*#&%@" in the textbox the search function is never going to find anything because there isn't a cell that containing those characters. Instead, the userform freezes because it continues to search forever. Can i specify that if it doesnt contain any of those characters to display an error message and then exit sub, and can i also add a timeoout if it searches to long?

Any ideas?

[ATTACH=CONFIG]69620[/ATTACH]

the problem is I search "****" AKA anything that I know is not in my search range, and crange is somehow = "equipment" which should not be true but because I told it as along as it equals anything, continue down the list.

This is the error produced, Im not sure how to fix this.

in case anyone cannot read the error it says "run-time error 1004" & " unable to get the FindNext property of the range class"

Thanks again for everyone's help!