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

TullulahJoy

New Member
Joined
Jul 1, 2018
Messages
6
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.

VBA Code:
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,523
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
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

New Member
Joined
Jul 1, 2018
Messages
6
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,971
Office Version
  1. 2016
Platform
  1. Windows
(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

New Member
Joined
Jul 1, 2018
Messages
6
YES!!! That works perfectly! Thank you so much.

The final code is:

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,523
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,467
Messages
5,636,456
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top