Excel 如果没有退出循环

Question

我有以下 Do带有嵌套 If Not陈述:

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

这可以正常运行，但是在应该退出循环时却失败了。当我通过它时，它会捕获 If Not i并跳过复制命令，但仍会逐步执行下面的行并在 Selection.PasteSpecial 上失败.我似乎无法让它跳过这些并继续下一个 Do .以下工作，但我需要在删除之前复制:

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

如何让循环注册“#609532”不再存在并继续下一个循环？

Answer 1

您需要使用 If .. Then .. End If而是声明 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

最好避免 Select和 Activate声明:

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