在一张纸上查找单元格并将行复制到另一张纸上

我有一张名为 Backlog 的工作表，其中包含数据行和列。我需要在倒数第二列中逐行搜索#N/A 的代码。当它发现 #N/A 时，需要检查最后一列是否包含 C。如果它包含 C，则应将整行附加到名为 Logoff 的表中。如果最后一列不包含 C，则应将整行附加到名为 Denied 的工作表中。一旦移动到“注销”或“拒绝”，应从原始待办事项表中删除该行。我下面的代码不起作用。在第一个 For 语句之后，它转到 End Sub，但没有任何编译错误。

Private Sub CommandButton2_Click()
    Dim IMBacklogSh As Worksheet
    Set IMBacklogSh = ThisWorkbook.Worksheets("Backlog")
    Dim logoffSh As Worksheet
    Set logoffSh = ThisWorkbook.Worksheets("Claims Logged off")
    Dim deniedsh As Worksheet
    Set deniedsh = ThisWorkbook.Worksheets("Claims Denied")

    IMBacklogSh.Select
    Dim i As Long
    For i = 3 To Cells(Rows.Count, 13).End(xlUp).Row
        If Cells(i, 13).Value = "#N/A" Then
            If Cells(i, 14).Value = "C" Then
            IMBacklogSh.Rows(i).EntireRow.Copy Destination:=logoffSh.Range("A" & logoffsh.Cells(Rows.Count, "A").End(xlUp).Row + 1)
            Else
            IMBacklogSh.Rows(i).EntireRow.Copy Destination:=deniedsh.Range("A" & deniedsh.Cells(Rows.Count, "A").End(xlUp).Row + 1)
            End If
        End If
    Next i
End Sub

尝试一下作为If Cells(i, 13).Text = "#N/A" Then . #N/A是错误代码，而不是值；但是，那范围.文本属性 https://msdn.microsoft.com/en-us/library/office/ff840217.aspx可以检查或IsError 函数 https://msdn.microsoft.com/en-us/library/office/gg278547.aspx可用于检查单元格内容是否有任何错误。

    If Cells(i, 13).Text = "#N/A" Then
    'Alternate with IsError
    'If IsError(Cells(i, 13)) Then
        If Cells(i, 14).Value = "C" Then
            IMBacklogSh.Rows(i).EntireRow.Copy _
                Destination:=logoffSh.Range("A" & logoffsh.Cells(Rows.Count, "A").End(xlUp).Row + 1)
        Else
            IMBacklogSh.Rows(i).EntireRow.Copy _
                Destination:=deniedsh.Range("A" & deniedsh.Cells(Rows.Count, "A").End(xlUp).Row + 1)
        End If
    End If

然而，单个细胞检查是不必要且耗时的。这自动过滤方法 https://msdn.microsoft.com/en-us/library/office/aa221844.aspx可以用来隔离#N/A with C and #N/A with <>C.

Private Sub CommandButton2_Click()
    Dim IMBacklogSh As Worksheet, logoffSh As Worksheet, deniedsh As Worksheet

    Set IMBacklogSh = ThisWorkbook.Worksheets("Backlog")
    Set logoffSh = ThisWorkbook.Worksheets("Claims Logged off")
    Set deniedsh = ThisWorkbook.Worksheets("Claims Denied")

    With IMBacklogSh
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            .AutoFilter field:=13, Criteria1:="#N/A"
            .AutoFilter field:=14, Criteria1:="C"
            With .Resize(.Rows.Count - 1, Columns.Count).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    .Copy Destination:= _
                        logoffSh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                    'optionally delete the originals
                    .EntireRow.Delete
                End If
            End With
            .AutoFilter field:=14, Criteria1:="<>C"
            With .Resize(.Rows.Count - 1, Columns.Count).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    .Copy Destination:= _
                        deniedsh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                    'optionally delete the originals
                    .EntireRow.Delete
                End If
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub