在一张纸上查找单元格，并将行复制到另一张纸上 [英] Find cells on one sheet and copy the rows to another sheet

问题描述

我有一个名为Backlog的工作表，其中包含行和数据列。我需要在第2到最后一列逐行搜索寻找＃N / A的代码。当它找到＃N / A时，如果它包含C，则需要检查最后一列。如果它包含一个C，那么整行应该被附加到一个名为Logoff的工作表上。如果最后一列不包含C，则整行将被附加到名为Denied的工作表中。移动到注销或拒绝后，应从原始的备用工作表中删除该行。下面的代码不工作。在第一个For Statement之后，它转到End Sub，但没有任何编译错误。

  Private Sub CommandButton2_Click（）
 Dim IMBacklogSh As Worksheet 
设置IMBacklogSh = ThisWorkbook.Worksheets（Backlog）
 Dim signffSh As Worksheet 
设置logoffSh = ThisWorkbook.Worksheets（Claim loggeded off）
 Dim deniedsh As Worksheet 
设置deniedsh = ThisWorkbook.Worksheets（Claim Denied）
 
 IMBacklogSh.Select 
 Dim i As Long 
 For i = 3 To Cells（ Rows.Count，13）.End（xlUp）.Row 
如果Cells（i，13）.Value =＃N / A然后
如果Cells（i，14）.Value =C 然后
 IMBacklogSh.Rows（i）.EntireRow.Copy Destination：= logoffSh.Range（A& logoffsh.Cells（Rows.Count，A）。End（xlUp）.Row + 1） 
 Else 
 IMBacklogSh.Rows（i）.EntireRow.Copy目的地：= deniedsh.Range（A& deniedsh.Cells（Rows.Count，A）。End（xlUp）.Row + 1）
如果$ b $结束b结束If 
 Next i 
 End Sub 
  

解决方案，请尝试使用。 ＃N / A 是错误代码，而不是值;但是，可以检查 Range.Text属性或<可以使用href =https://msdn.microsoft.com/en-us/library/office/gg278547.aspx =nofollow> IsError函数来检查单元格的内容是否有任何错误。

 如果Cells（i，13）.Text =＃N / A然后
'与IsError交替b $ b'如果IsError（Cells（i，13））然后
如果Cells（i，14）.Value =C然后
 IMBacklogSh.Rows（i）.EntireRow.Copy _ 
 Destination：= logoffSh.Range（A& logoffsh.Cells（Rows.Count，A）。End（xlUp）.Row + 1）
 Else 
 IMBacklogSh.Rows ）.EntireRow.Copy _ 
目标：= deniedsh.Range（A& deniedsh.Cells（Rows.Count，A）。End（xlUp）.Row + 1）
 End If 
结束如果
  

然而，单独的单元格检查是不必要和耗时的。可以使用自动筛选器方法隔离＃N / A 与 C 和＃N / A 与 C 。

  Private Sub CommandButton2_Click（）
 Dim IMBacklogSh作为工作表，注销作为工作表，deniedsh作为工作表
 
设置IMBacklogSh = ThisWorkbook.Worksheets（Backlog）
设置logoffSh = ThisWorkbook.Worksheets（Claim loggeded）
设置deniedsh = ThisWorkbook.Worksheets（Claims Denied）
 
与IMBacklogSh 
如果.AutoFilterMode然后.AutoFilterMode = False 
带.Cells（1，1）.CurrentRegion 
 .AutoFilter字段：= 13，Criteria1：=＃N / A
 .AutoFilter字段：= 14，Criteria1：=C
使用.Resize（.Rows.Count  - Columns.Count）.Offset（1，0）
如果CBool​​（Application.Subtotal（103，.Cells））然后
 .Copy目的地：= _ 
 logoffSh.Cells（Rows.Count，A）。End（xlUp）.Offset（1，0）
'可选地删除原始文件
 .EntireRow.Delete 
 End If 
 End With 
 .AutoFilter field：= 14，Criteria1：=" C
 With .Resize（.Rows.Count  -  1，Columns.Count ）.Offset（1，0）
如果CBool​​（Application.Subtotal（103，.Cells））然后
 .Copy目标：= _ 
 deniedsh.Cells（Rows.Count，A ）.End（xlUp）.Offset（1，0）
'可选地删除原始文件
 .EntireRow.Delete 
 End If 
 End With 
 End With 
如果.AutoFilterMode然后.AutoFilterMode = False 
结束
 End Sub 
  

I have a sheet called Backlog containing rows and columns of data. I need code that will search row by row in the 2nd to last column looking for #N/A. When it finds #N/A it needs to check the last column if it contains a C or not. If it contains a C then the whole row should be appended to a sheet called Logoff. If the last column does not contain a C then the whole row should be appended to a sheet called Denied. The row should be deleted from the original Backlog sheet once moved to either Logoff or Denied. The code I have below is not working. After the first For Statement it goes to End Sub, but there is not any compiling errors.

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

解决方案

Try it as If Cells(i, 13).Text = "#N/A" Then . #N/A is an error code, not a value; however, the Range.Text property can be examined or the IsError function could be used to examine the cell's contents for any error.

    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

However, individual cell examination is not necessary and time consuming. The AutoFilter method can be used to isolate #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

这篇关于在一张纸上查找单元格，并将行复制到另一张纸上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！