批量删除目标单元格时VBA触发Type 13类型不匹配错误

错误原因 #

批量删除多个单元格时，Target是包含多单元格的区域，此时Target.Value返回的是数组类型，直接和字符串（比如"Wind Sites"）做相等判断，就会触发Type 13类型不匹配错误。另外原代码存在大量冗余判断，逻辑重复且混乱，进一步放大了问题。

优化后的代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ChangedCells As Range
    Dim Cell As Range
    
    ' 获取C列中被修改的单元格区域
    Set ChangedCells = Intersect(Target, Me.Range("C:C"))
    If ChangedCells Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    ' 遍历每个被修改的单元格，逐个处理
    For Each Cell In ChangedCells
        ' 处理"Wind Sites"或"Springbok 3"的情况
        If Cell.Value = "Wind Sites" Or Cell.Value = "Springbok 3" Then
            Cell.Offset(0, 2) = "N/A"
            Cell.Offset(0, 3) = "N/A"
            Cell.Offset(0, 4) = "N/A"
            Cell.Offset(0, 5) = "N/A"
            Cell.Offset(0, 7) = "N/A"
            Cell.Offset(0, 9) = "N/A"
            Cell.Offset(0, 10) = "N/A"
        Else
            ' 清空指定单元格
            Cell.Offset(0, 2).ClearContents
            Cell.Offset(0, 3).ClearContents
            Cell.Offset(0, 4).ClearContents
            Cell.Offset(0, 5).ClearContents
            Cell.Offset(0, 7).ClearContents
            Cell.Offset(0, 9).ClearContents
            Cell.Offset(0, 10).ClearContents
        End If
        
        ' 处理"Galloway 1"的情况
        Select Case Cell.Value
            Case "Galloway 1"
                Cell.Offset(0, 8) = ""
            Case ""
                Cell.Offset(0, 8).ClearContents
            Case Else
                Cell.Offset(0, 8) = "N/A"
        End Select
    Next Cell
    
    Application.EnableEvents = True
End Sub

关键修改说明 #

• 先筛选出C列中实际被修改的单元格区域，避免无关操作
• 遍历每个单元格，确保每次处理单个单元格，彻底解决多单元格Value类型不匹配问题
• 用Select Case替代嵌套的If判断，简化"Galloway 1"相关逻辑，可读性更强
• 移除原代码中重复的Target.Count > 1和Intersect判断，减少冗余
• 统一管理Application.EnableEvents的开关，防止事件循环触发

内容的提问来源于stack exchange，提问作者Jphillip82