运行VBA清除空值公式单元格时遇类型不匹配错误13求助

错误根源是部分公式单元格返回了错误值（比如#N/A、#DIV/0!等，即便用了IFERROR也可能存在未覆盖的异常场景），此时cF.Value的类型为Error，和空字符串""直接比较会触发类型不匹配。

修改后的代码如下，核心是先排除错误值，再判断单元格内容是否为空：

Sub clearFormulaEmptyCells()
 Dim sh As Worksheet, rngForm As Range, rngDel As Range, cF As Range
 
 Set sh = ActiveSheet
 On Error Resume Next
  Set rngForm = sh.UsedRange.SpecialCells(xlCellTypeFormulas)
 On Error GoTo 0
 If rngForm Is Nothing Then Exit Sub
 For Each cF In rngForm.Cells
    ' 先排除错误值，再判断是否为空字符串
    If Not IsError(cF.Value) And cF.Value = "" Then
        If rngDel Is Nothing Then
            Set rngDel = cF
        Else
            Set rngDel = Union(rngDel, cF)
        End If
    End If
 Next
 If Not rngDel Is Nothing Then rngDel.ClearContents
End Sub

额外优化建议（针对大表格） #

如果你的Excel文件数据量很大，逐个单元格循环会影响效率，可改用数组批量处理，减少VBA与Excel界面的交互次数：

Sub clearFormulaEmptyCellsFast()
    Dim sh As Worksheet
    Dim formulaRng As Range, cellVals As Variant
    Dim i As Long, j As Long
    Dim rngDel As Range
    
    Set sh = ActiveSheet
    On Error Resume Next
    Set formulaRng = sh.UsedRange.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If formulaRng Is Nothing Then Exit Sub
    
    ' 将区域值存入数组，提升处理速度
    cellVals = formulaRng.Value
    
    For i = 1 To UBound(cellVals, 1)
        For j = 1 To UBound(cellVals, 2)
            If Not IsError(cellVals(i, j)) And cellVals(i, j) = "" Then
                If rngDel Is Nothing Then
                    Set rngDel = formulaRng.Cells(i, j)
                Else
                    Set rngDel = Union(rngDel, formulaRng.Cells(i, j))
                End If
            End If
        Next j
    Next i
    
    If Not rngDel Is Nothing Then rngDel.ClearContents
End Sub

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