Run-time error '13': Type mismatch on VBA code

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

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.

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.

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.

EDIT 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:

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

EDIT 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.

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

Solution

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...