时间戳

首页 > 解决方案 > 运行时错误“13”：清除单元格内容时类型不匹配

excel - 运行时错误“13”：清除单元格内容时类型不匹配

问题描述

我对 VBA 很陌生，但到目前为止，我已经能够自己完成我需要的一切。但是，我似乎无法解决这个问题。从技术上讲，我有 2 个不同的私人潜艇（Worksheet_Change），但只有将它们结合起来才能让它们工作。第一个将 B 列中具有相同内容的单元格的数量限制为 3 个。

第二个清除范围 G：I，如果 H = 100，则复制范围 A：F。当我清除某些内容时，“Macro1”给我这个运行时错误（运行时错误'13'：类型不匹配）原因。显然，“Macro2”是在清除内容，所以我真的在和自己作对。

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

标签： excelvbamismatch

解决方案

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.

推荐阅读