vba - ＃DIV/0!错误并将其替换为零

Question

我正在尝试下面的代码来检测DIV/0错误，并将其替换为零或空单元格。但是**行让我出错

Dim Cell As Range
Dim iSheet as Worksheet
For Each iSheet In sheets(Array("Sheet1", "Sheet2", "Sheet3"))
    With iSheet
        For Each Cell In .UsedRange
            **If Cell.Value = CVErr(xlErrDiv0) Then Cell.Value = 0**
        Next Cell
    End With
Next iSheet

Answer 1

似乎您必须先将cell.Value检查为错误，然后再将其与错误值进行比较

If IsError(Cell.Value) Then If Cell.Value = CVErr(xlErrDiv0) Then Cell.Value = 0

所以你的代码变成

Sub mm()
    Dim Cell As Range
    Dim iSheet As Worksheet
    For Each iSheet In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        With iSheet
            For Each Cell In .UsedRange
                If IsError(Cell.Value) Then If Cell.Value = CVErr(xlErrDiv0) Then Cell.Value = 0
            Next Cell
        End With
    Next iSheet
End Sub

限制单元格循环的另一种可能性是

Sub mm()
    Dim Cell As Range
    Dim iSheet As Worksheet

    For Each iSheet In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        With iSheet
            If WorksheetFunction.CountIf(.UsedRange, "#DIV/0!") Then ' if any Division error found in current sheet used range cells
                For Each Cell In .UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors) 'loop through error cells only
                    If Cell.Value = CVErr(xlErrDiv0) Then Cell.Value = 0
                Next
            End If
        End With
    Next
End Sub