Run-time Error '13': Type Mismatch when I clear cell contents

Question

I'm very new to VBA, but so far I've been able to work myself through everything I'd need. However, I can't seem to sort-out this issue. I technically have 2 different private subs (Worksheet_Change), but I could only get them to work if I combined them. The first one limits the number of cells with identical content in column B to 3.

The second clears range G:I, and copies range A:F if the value of H = 100. "Macro1" gives me this run-time error (Run-time Error '13': Type Mismatch) when I clear contents for some reason. Obviously, "Macro2" is clearing contents, so I'm really working against myself.

Private Sub Worksheet_Change(ByVal Target As Range)

'First Macro. (The issue is in this Macro)

If WorksheetFunction.CountIf(Range("B4:B350"), Target) > 3 Then

    msgbox "This team member has the maximum number of open projects.", vbCritical, "Overburdened"
    Target.Value = ""
    Target.Select

End If

'Second Macro

    Dim rngStart As Range

    Set rngStart = ActiveCell

    A = Worksheets("Project Tracking").Cells(Rows.Count, 1).End(xlUp).Row


For i = 4 To A

    If Worksheets("Project Tracking").Cells(i, 8).Value = 100 Then

        Worksheets("Project Tracking").Range(Cells(i, 7), Cells(i, 9)).ClearContents
        Worksheets("Project Tracking").Range(Cells(i, 1), Cells(i, 6)).Copy
        Worksheets("Completed Projects").Activate
        B = Worksheets("Completed Projects").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Completed Projects").Cells(B + 1, 1).Select
        ActiveSheet.Paste
        Worksheets("Project Tracking").Activate
        Worksheets("Project Tracking").Range(Cells(i, 1), Cells(i, 6)).ClearContents

        Call Reset_List

        Call Macro3

    End If

Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Project Tracking").Cells(1, 1).Select

rngStart.Select


End Sub

Quick test - See how you (correctly!) qualified the Range() with the Worksheet "Project Tracking"? When you do that with Range(), you need to do that with the Cells() reference in there as well. So just do Worksheets("Project Tracking").Range(Worksheets("Project Tracking").Cells(i, 1), Worksheets("Project Tracking").Cells(i, 6)).ClearContents — BruceWayne
If you are sure the issue is because the _Change() event is causing changes (i.e. calling itself), you should be able to prevent it by placing Application.EnableEvents = False at the beginning of the Sub and Application.EnableEvents = True at the end. — Mistella

Comintern Comintern · Accepted Answer · 2018-11-30T22:28:08

You get a type mismatch on this line...

If WorksheetFunction.CountIf(Range("B4:B350"), Target) > 3 Then

...because you are calling the default member of Target (which is .Value) and then passing that to CountIf. The problem is that because you're in the Worksheet_Change event handler, the Target doesn't have to be a single cell. If it isn't a single cell, Target.Value contains an array, which throws if you try to pass it to CountIf. If you're only interested in single cell changes, put a guard clause at the top of the Sub:

If Target.Cells.Count <> 1 Then Exit Sub

Also, as noted in the comments, since you are changing cells (although it's not clear which sheet this is in), you should disable events before you start making changes that can cause re-entry - Application.EnableEvents = False. Don't forget to re-enable it when you're done.

Run-time Error '13': Type Mismatch when I clear cell contents

1 Answers