Excel If Not Failing to exit Loop

Question

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?

Siddharth Rout · Accepted Answer · 2014-01-14 13:24:43Z

5

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

edited Jan 14, 2014 at 13:24

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

answered Jan 14, 2014 at 13:15

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Siddharth Rout Over a year ago

Damn! Too Late... I will have to discard my answer :D BTW no need of .Activate :)

Siddharth Rout Over a year ago

Or .Select INTERESTING READ

Dmitry Pavliv Over a year ago

@SiddharthRout, I've just updated answer with code avoiding Select and Activate statements

Dmitry Pavliv Over a year ago

@Benjooster, have you use this line of code nextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 or this one nextRow = Cells(.Rows.Count, 1).End(xlUp).Row + 1 (note that there is no . before Cells)?

Benjooster Over a year ago

@simoco Totally missed that. Thanks.

|

Collectives™ on Stack Overflow

Excel If Not Failing to exit Loop

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related