运行时错误“13”:VBA代码上的类型不匹配

I'm using the following code in a project that I'm working on:

我正在一个项目中使用以下代码:

Sub test()
    Application.ScreenUpdating = False
    Worksheets("Technician Report Summary").Range("G2:I8561").ClearContents
    Dim Source As Range, Target As Range
    Dim n As Long, i As Long
    n = Range("C:C").Cells.Count
    i = Cells(n, "C").End(xlUp).Row
    Set Source = Range(Cells(1, "C"), Cells(n, "E"))
    Set Target = Range("G2:I2")

    For i = 2 To n
        If Source.Cells(i, 1).Value <> "Not Used" And Not Application.WorksheetFunction.IsNA(Source.Cells(i, 3).Value) Then
            Source.Rows(i).Copy
            Target.PasteSpecial xlPasteValues
            Set Target = Target.Offset(1)
        End If
    Next i

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

When I run the code it appears to operate appropriately, it copies a list of filtered values following the criteria set in the code to another spot in the same worksheet. The problem is that I also get the following error:

当我运行代码时，它似乎在适当地操作，它会根据代码中的标准将筛选值列表复制到同一个工作表中的另一个位置。问题是我也得到了以下错误:

Run-time error '13': Type mismatch

运行时错误'13':类型不匹配

What have I got wrong? I think that I've made some kind of error in declaring the variables or something to that effect, but I'm not familiar enough with VBA to pinpoint quite what the problem is.

我哪里错了?我认为我在声明变量或类似的东西时犯了一些错误，但我对VBA还不够熟悉，无法准确指出问题所在。

Any help would be appreciated.

如有任何帮助，我们将不胜感激。

EDIT:

编辑:

Sorry for not being clear.

对不起，我没说清楚。

I have a set of values in the worksheet 'Technician Report Summary' that occupies the range C2:E8561. There's some erroneous values that I want to have filtered out and copied, these are "Not Used" and '#N/A' errors. The code above copies the values and then pastes them next to the original, unfiltered set of values with the instances of "Not Used" and '#N/A' removed in columns G, H, and I.

我在工作表“技术员报告摘要”中有一组值，该值位于C2:E8561范围内。我想过滤和复制一些错误的值，它们是“未使用”和“#N/A”错误。上面的代码复制这些值，然后将它们粘贴到原始的、未过滤的值集旁边，并在列G、H和I中删除“未使用”和“#N/A”的实例。

No line is highlighted as being the problem when I run it, the error dialog just pops up and when I hit "OK" nothing is highlighted.

当我运行它时，没有高亮显示出问题，错误对话框就会弹出，当我点击“OK”时，什么都没有突出显示。

EDIT 2:

编辑2:

I made the change that @KFitchter suggest and now it seems to lock-up. I hit the escape key and the following line was highlighted:

我做出了@KFitchter建议的改变，现在似乎锁定了。我按下escape键，下面一行高亮显示:

If Source.Cells(i, 1).Text <> "Not Used" And Not Application.WorksheetFunction.IsNA(Source.Cells(i, 3).Value) Then

EDIT 3:

编辑3:

Just thought of something else that could be messing with it. The values in columns C,D and E are actually functions pulling values from elsewhere in the workbook. Here's an example of one of those functions.

想想别的什么东西可能会把它搞砸。列C、D和E中的值实际上是从工作簿其他地方提取值的函数。这是其中一个函数的例子。

=INDEX('RAW DATA'!$A:$A,$B2)

=指数(“原始数据”! A:美元,美元B2)

2 个解决方案

#1

So I'm not exactly sure what the problem is.

所以我不确定问题是什么。

I modified your code just a tiny bit (using my original guess) to:

我修改了你的代码一点点(用我原来的猜测)到:

Sub test()
    Application.ScreenUpdating = False
    Worksheets("Technician Report Summary").Range("G2:I8561").ClearContents
    Dim Source As Range, Target As Range
    Dim n As Long, i As Long
    n = Range("C:C").Cells.Count
    i = Cells(n, "C").End(xlUp).Row
    Set Source = Range(Cells(1, "C"), Cells(n, "E"))
    Set Target = Range("G2:I2")

    For i = 2 To n
        If Source.Cells(i, 1).Text <> "Not Used" And Not Application.WorksheetFunction.IsNA(Source.Cells(i, 3).Value) Then
            Source.Rows(i).Copy
            Target.PasteSpecial xlPasteValues
            Set Target = Target.Offset(1)
        End If
    Next i

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Where the only real difference is "Value" to "Text". I'm not getting any errors. The only thing is that it's taking a long while to do what it's supposed to do because there are so many lines.

真正的区别是“值”与“文本”之间的区别。我没有得到任何错误。唯一的事情是它花了很长时间去做它应该做的事情，因为有很多行。

Now... I would suggest maybe doing what you're doing a little differently.

现在…我建议你做一些不同的事情。

Sub test()
    Application.ScreenUpdating = False

    Worksheets("Technician Report Summary").Range("G2:I8561").ClearContents

    Dim Source As Range, Target As Range
    Dim n As Long

    ActiveSheet.Range("C:E").AutoFilter Field:=1, Criteria1:= _
        Array("<>#N/A", "<>Not Used"), Operator:=xlAnd
    ActiveSheet.Range("C:E").AutoFilter Field:=3, Criteria1:= _
        "<>#N/A", Operator:=xlAnd

    n = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row

    Set Source = Range("C2:E" & n).SpecialCells(xlCellTypeVisible)
    Set Target = Range("G2")

    Source.Copy
    Target.PasteSpecial (xlPasteValues)

    Application.CutCopyMode = False
    Application.ScreenUpdating = True

    Range("C:E").AutoFilter
End Sub

This basically does what you were trying to do, but with autofilter. It's much faster than looping through every single cell and does the same thing in the end. I hope that helps...

这基本上就是你想做的，但是用自动过滤器。它比在每个单元格中循环要快得多，而且最终会做同样的事情。我希望帮助……

#2

Start by specifying the parent of all of your range and cell references.

首先指定所有范围和单元格引用的父元素。

Do not combine error checking with other string comparisons or boolean checks. Check for an error first and then process other boolean checks on a new code line.

不要将错误检查与其他字符串比较或布尔检查相结合。首先检查错误，然后在新的代码行上处理其他布尔检查。

The line n = .Range("C:C").Cells.Count puts 1,048,576 into n. Do you really want Set src = .Range("C1:E1048576) and For i = 2 To 1048576 ?

line n = . range ("C:C"). cell。Count将1048,576代入n，你真的想要将src = . range ("C1:E1048576 ")和i = 2到1048576 ?

Direct text comparisons are usually case-sensitive.

直接文本比较通常区分大小写。

Use direct value transfer over Copy, Paste Special, Values whenever possible.

尽可能使用直接的值转移复制，粘贴特殊的值。

Put all of those points together as:

把所有这些点放在一起作为:

Sub test()
    Dim src As Range, trgt As Range, nas As Range
    Dim n As Long, i As Long, bERR As Boolean

    Application.ScreenUpdating = False

    With Worksheets("Technician Report Summary")
        .Range("G2:I8561").ClearContents
        n = .Cells(Rows.Count, "C").End(xlUp).Row
        Set src = .Range(.Cells(1, "C"), .Cells(n, "E"))
        Set trgt = .Range("G2:I2")
    End With

    For i = 2 To n
        If Not IsError(src.Cells(i, 3)) Then
            Debug.Print src.Cells(i, 3).Text
            If LCase(src.Cells(i, 1).Value2) <> "not used" Then
                trgt = src.Rows(i).Value
                Set trgt = trgt.Offset(1)
            End If
        End If
    Next i

    Application.ScreenUpdating = True
End Sub

That should be enough to get you working.

这就足够让你工作了。

#1