Home #DIV/0! error and replace it with zero
Reply: 1

#DIV/0! error and replace it with zero

shweta agnihotri
1#
shweta agnihotri Published in 2018-02-14 17:43:14Z

I am trying the below code to detect DIV/0 error and replace it with Zero or empty cell. but ** line is throwing me error

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
DisplayName
2#
DisplayName Reply to 2018-02-14 18:09:48Z

seem like you have to check for cell.Value being an error before comparing it to an error value

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

so your code becomes

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

another possibility, which limits the cell loop, is the following

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 8.130412 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO