CODEWITHSUNDEEP
excelvba
Benjooster
Benjooster

Reputation: 552

Excel If Not Failing to exit Loop

I have the following Do with a nested If Not statement:

Do
        Set i = SrchRng.Find("#609532", LookIn:=xlValues)
        If Not i Is Nothing Then i.EntireRow.Copy
            BBsheet.Activate
            nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(nextRow, 1).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
            srcBook.Activate
            i.EntireRow.Delete
    Loop While Not i Is Nothing

This functions properly but it is failing to exit the loop when it should. When I step through it, it grabs the If Not i and skips over the copy command, but still steps through the lines below and fails on the Selection.PasteSpecial. I can not seem to get it to skip over those and move on to the next Do. The following works, but I need to copy before the delete:

Do
        Set i = SrchRng.Find("#609532", LookIn:=xlValues)
        If Not i Is Nothing Then i.EntireRow.Delete
    Loop While Not i Is Nothing

How do I get the loop to register that "#609532" no longer exists and move on to the next loop?

Upvotes: 1

Views: 76

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

You need to use If .. Then .. End If statement instead If ... Then ..:

Do
    Set i = SrchRng.Find("#609532", LookIn:=xlValues)
    If Not i Is Nothing Then 
        i.EntireRow.Copy
        BBsheet.Activate
        nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
        Cells(nextRow, 1).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        srcBook.Activate
        i.EntireRow.Delete
    End If
Loop While Not i Is Nothing

and it's better to avoid Select and Activate statements:

Do
    Set i = SrchRng.Find("#609532", LookIn:=xlValues)
    If Not i Is Nothing Then
        i.EntireRow.Copy
        With BBsheet
            nextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Cells(nextRow, 1).PasteSpecial Paste:=xlValues
        End With
        i.EntireRow.Delete
    End If
Loop While Not i Is Nothing

Upvotes: 5

Related Questions