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?

score 5 · Accepted Answer · edited Jan 14 '14 at 13:24

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 '14 at 13:24

Siddharth Rout

147,039
17
206
250

answered Jan 14 '14 at 13:15

Dmitry Pavliv

35,333
13
79
80

1

Damn! Too Late... I will have to discard my answer :D BTW no need of `.Activate` :) – Siddharth Rout Jan 14 '14 at 13:16
4

Or `.Select` [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) – Siddharth Rout Jan 14 '14 at 13:17
@SiddharthRout, I've just updated answer with code avoiding `Select` and `Activate` statements – Dmitry Pavliv Jan 14 '14 at 13:21
@SiddharthRout, and I should save your "Interesting Read" link for the future answers:) – Dmitry Pavliv Jan 14 '14 at 13:23
That's great info, and I've heard the whole don't use `.Activate` argument before, but your second block of code starts pasting on line 998 of the excel sheet. Any idea how to start at the top of that sheet without using `.Activate`? – Benjooster Jan 14 '14 at 13:39
@Benjooster, Have you used last code (my answer was updated 17 min ago)?. As I see, second block of code should do the same as first one. You tries to paste values using `nextRow` variable, which is defined as follows: `nextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1`. This line means that `nextRow` will be equal to the first _empty_ row in column 'A'. If code pastes values starting from 998 line, it means that your `A997` cell not empty – Dmitry Pavliv Jan 14 '14 at 13:44
@simoco Yes that was my assumption as well. Clearing the sheet didn't resolve the issue, and even more confusing the first block of code starts at Row 2. – Benjooster Jan 14 '14 at 13:47
1

@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`)? – Dmitry Pavliv Jan 14 '14 at 13:48
1

@simoco Totally missed that. Thanks. – Benjooster Jan 14 '14 at 13:55

Excel If Not Failing to exit Loop

1 Answers1