Code not looping through sheets

Question

The following code is deleting too much on first sheet and then not looping to second sheet?? Error appearing on .FindNext statement.

Sub FindAndExecute3()
Dim Loc As Range
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
    With sh.UsedRange
        Set Loc = .Cells.Find(What:="AUTO.WHSE.")
        If Not Loc Is Nothing Then
          Do Until Loc Is Nothing
            Rows(ActiveCell.Row & ":" & (ActiveCell.Row + 2)).Delete
            Set Loc = .FindNext(Loc)
          Loop
        End If
    End With
    Set Loc = Nothing
Next
End Sub

how can we know what is not correct if you don't tell what you would like to achieve with your code? And can you provide the text of the error? — moffeltje, Jun 12 '15 at 14:37

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

You have to fully qualify your Ranges. This issue shows up once and again (e.g., this).

What does this mean? Do not use Cells, Range, Rows or Columns without specifying which Worksheet they belong to, unless you specifically want to do that (and even in that case, explicitly using ActiveSheet improves readability and reduces the chances of errors, similar to using Option Explicit). For instance, replace

Rows(ActiveCell.Row & ":" & (ActiveCell.Row + 2)).Delete

with

sh.Rows(sh.<something>.Row & ":" & (sh.<something>.Row + 2)).Delete

I am assuming the Range to be deleted spans Row numbers taken from something related to sh.

Note 1: In this case, even without qualifying Ranges, code continues execution and the mistake may go unnoticed, as it produces a valid result. In other cases, code without fully qualified Ranges will throw an error (e.g., with something like sheet1.Range(Cells(..., when sheet1 is not the ActiveSheet).**

Note 2: You can work with the ActiveCell only when the Worksheet that it is on is the ActiveSheet [MSDN].

edited May 23 '17 at 12:05

Community

1
1

answered Jun 12 '15 at 15:18

sancho.s ReinstateMonicaCellio

14,708
20
93
185

You also need to record the first found address and check when you've returned to it, otherwise the Do...Loop will continue. https://msdn.microsoft.com/en-us/library/office/ff839746.aspx – Darren Bartrup-Cook Jun 12 '15 at 15:31
Thank you all for your questions and efforts. I have updated the code a little bit. It is looping through the sheets, but when there are 2 instance of AUTO.WHSE., it only acts on the first one. – user5003326 Jun 13 '15 at 17:07
Not sure how to enter this code, sorry, new to stack. [code]Sub deleteRows() Dim ws As Worksheet On Error Resume Next Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets ws.Activate Cells.Find(What:="AUTO.WHSE.", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate Rows(ActiveCell.Row & ":" & (ActiveCell.Row + 2)).Delete Next ws Application.ScreenUpdating = True End Sub[/code] – user5003326 Jun 13 '15 at 17:08
It looks like you are carrying out only one `Find`. And check my Note 2. – sancho.s ReinstateMonicaCellio Jun 14 '15 at 02:24

Code not looping through sheets

1 Answers1

Linked