粘贴整行数据时 VBA 类型不匹配(运行时错误 13)

Question

我目前遇到问题，但我不知道为什么..

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub

If Target.Value = "Yes" Then
    Range(Range("A" & Target.Row), Range("I" & Target.Row)).Copy _
    Sheets("UpdateModify Forms").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Range(Range("AC" & Target.Row), Range("AU" & Target.Row)).Copy _
    Sheets("UpdateModify Forms").Range("J" & Rows.Count).End(xlUp).Offset(1, 0)

ElseIf Target.Value = "No" Then
    Range(Range("A" & Target.Row), Range("AB" & Target.Row)).Copy _
    Sheets("Development Forms").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Range(Range("AW" & Target.Row), Range("AY" & Target.Row)).Copy _
    Sheets("Development Forms").Range("AC" & Rows.Count).End(xlUp).Offset(1, 0)

End If
End Sub

错误指向

If Target.Value = "Yes" Then

它应该如何工作:

当我粘贴填充有数据的行时，它应该正确地检查标准(是或否)，并将其排序到其独特的工作表中。

但是

由于某种原因出现了运行时错误13..我只知道如何绕过它，即手动输入数据并避免首先触发验证的列，然后首先输入其他数据，最后输入验证柱子。

还有一个问题:

有没有办法在我最后输入验证列的情况下打印整个内容？因为我尝试先键入验证列，当我继续填写旁边的行时，它不会显示在下一张纸上，只显示"is"或“否”，这是 B 列

如有任何帮助，我们将不胜感激，感谢您的宝贵时间!

Answer 1

如果更新整行，Target 将是整行。您无法测试整行是否为"is" - 您需要查看单个单元格。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Columns("B")) Is Nothing Then Exit Sub
    Dim cel As Range
    For Each cel In Intersect(Target, Columns("B:B")).Cells
        If cel.Value = "Yes" Then
            Range(Range("A" & cel.Row), Range("I" & cel.Row)).Copy _
                     Sheets("UpdateModify Forms").Range("A" & Sheets("UpdateModify Forms").Rows.Count).End(xlUp).Offset(1, 0)
            Range(Range("AC" & cel.Row), Range("AU" & cel.Row)).Copy _
                     Sheets("UpdateModify Forms").Range("J" & Sheets("UpdateModify Forms").Rows.Count).End(xlUp).Offset(1, 0)    
        ElseIf cel.Value = "No" Then
            Range(Range("A" & cel.Row), Range("AB" & cel.Row)).Copy _
                     Sheets("Development Forms").Range("A" & Sheets("Development Forms").Rows.Count).End(xlUp).Offset(1, 0)
            Range(Range("AW" & cel.Row), Range("AY" & cel.Row)).Copy _
                     Sheets("Development Forms").Range("AC" & Sheets("Development Forms").Rows.Count).End(xlUp).Offset(1, 0)    
        End If
    Next
End Sub

至于另一个问题，即是否有办法仅在填写整行后才进行此过程，我建议您有一个按钮，并让用户在完成时单击该按钮 - 从而避免Worksheet_Change 事件。或者至少让最后一列成为触发更改的列。

<小时/>

响应评论请求值:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
    Dim cel As Range
    For Each cel In Intersect(Target, Columns("B:B")).Cells
        If cel.Value = "Yes" Then
            With Sheets("UpdateModify Forms")
                With .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).EntireRow
                    .Range("A1:I1").Value = Rows(cel.Row).Range("A1:I1").Value
                'I commented out the next two lines because I assume the
                'last row in column A should be the same as the last row
                'in column J - uncomment them if that is not the case.
                'End With
                'With .Range("J" & .Rows.Count).End(xlUp).Offset(1, 0).EntireRow
                    .Range("J1:AB1").Value = Rows(cel.Row).Range("AC1:AU1").Value
                End With
            End With
        ElseIf cel.Value = "No" Then
            With Sheets("Development Forms")
                With .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).EntireRow
                    .Range("A1:AB1").Value = Rows(cel.Row).Range("A1:AB1").Value
                'End With
                'With .Range("AC" & .Rows.Count).End(xlUp).Offset(1, 0).EntireRow
                    .Range("AC1:AE1").Value = Rows(cel.Row).Range("AW1:AY1").Value
                End With
            End With
        End If
    Next
End Sub