"Exit For" nested in other "For Each" is breaking

[TullulahJoy]

Hi, I am trying to build a Macro that searches for a term in the data across a number of worksheets (from the 2nd to the last) and for all rows that contain the search term/partial match, in any column, it returns that row to the first sheet - so it functions like a results page.

I almost have it working, but I noticed with my test data that where the search term is in multiple cells in a row, its returning that same row multiple times! I do need it to search every cell, but I need it to move on after its found the term once in any given row.

I thought it would be as simple as breaking out of the "For each cell" part of the loop to push it to the next row, but when I put Exit For in, it returns errors for the Next Row and Next WSC. Any idea how to fix so if its copied a line, it just moves to the next rather than continuing to search that row?

Also, what is the simplest/shortest way to use a dynamic range rather than A1:AA1000? It's only the rows that need to be dynamic. I suspect it'll be something around the End(xlUp) but can't figure out the right phrasing.

Sub Search_and_Return()

Dim WSC As Integer
Dim WSC_Max As Integer
WSC_Max = Application.Sheets.Count

Dim Partial_Text As String
Dim MYRANGE As Range

Partial_Text = Worksheets("Sheet1").Cells(1, 1).Value

For WSC = 2 To WSC_Max
    Set MYRANGE = Worksheets(WSC).Range("A1:AA1000")

    For Each Row In MYRANGE
        For Each Cell In Row.Cells

            If InStr(LCase(Cell.Value), LCase(Partial_Text)) <> 0 Then
            Cell.EntireRow.Copy
            Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            End If
            
        Exit For


    Next Row
        
Next WSC

End Sub

 

[Fluff]

Try it like

Sub Search_and_Return()

Dim WSC As Integer
Dim WSC_Max As Integer
WSC_Max = Application.Sheets.Count

Dim Partial_Text As String
Dim MYRANGE As Range

Partial_Text = Worksheets("Sheet1").Cells(1, 1).Value

For WSC = 2 To WSC_Max
    Set MYRANGE = Worksheets(WSC).Range("A1:AA1000")

    For Each Row In MYRANGE
        For Each Cell In Row.Cells

            If InStr(LCase(Cell.Value), LCase(Partial_Text)) <> 0 Then
            Cell.EntireRow.Copy
            Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            Exit For
            End If
            
        Next Cell


    Next Row
        
Next WSC

End Sub

 

[TullulahJoy]

Thanks Fluff - unfortunately I tried that already, and it doesn't stop the same line from being pasted multiple times. I also tried adding it there as you have written and removing the Next Cell line, but then I just get errors about the Next Row and Next WSC. Also tried it outside of the if, with and without the Next Cell. None of that worked either!

 

[Rick Rothstein]

(Untested and after a quick look only) What happens if you change this line of code...

For Each Row In MYRANGE

to this...

For Each Row In MYRANGE.Rows

 

[TullulahJoy]

YES!!! That works perfectly! Thank you so much.

The final code is:

Sub Search_and_Return()

Dim WSC As Integer
Dim WSC_Max As Integer
WSC_Max = Application.Sheets.Count

Dim Partial_Text As String
Dim MYRANGE As Range

Partial_Text = Worksheets("Sheet1").Cells(1, 1).Value

For WSC = 2 To WSC_Max
    Set MYRANGE = Worksheets(WSC).Range("A1:AA1000")

    For Each Row In MYRANGE.Rows
        For Each Cell In Row.Cells

            If InStr(LCase(Cell.Value), LCase(Partial_Text)) <> 0 Then
            Cell.EntireRow.Copy
            Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            Exit For
            End If
            
        Next Cell
    Next Row
        
Next WSC

End Sub

 

[Fluff]

Glad we could help & thanks for the feedback.