Replace Zeros with Blanks in This Code

[Just Shan]

The below code replaces all the #Div/0! Errors to Zeros. I want the code the generate Blanks instead of Zeros. Thanks you, Shan


Sub ErrorHandlerWrapper()
Dim sht As Worksheet
Dim rngCell As Range
Dim strOldFormula As String
Dim strNewFormula As String
Dim rngErrorCells As Range

For Each sht In ActiveWorkbook.Worksheets

On Error Resume Next
Set rngErrorCells = Nothing
Set rngErrorCells = sht.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)

If Not rngErrorCells Is Nothing Then

On Error GoTo 0

For Each rngCell In sht.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).Cells

strOldFormula = Right$(rngCell.Formula, Len(rngCell.Formula) - 1)
strNewFormula = "=IF(ISERROR(" & strOldFormula & "),0," & strOldFormula & ")"

rngCell.Formula = strNewFormula

Next rngCell

End If

Next sht

End Sub

[BrianB]

It is not good practice to put a blank (empty string) into a numeric cell.
It is better to reformat the cell to show blank for zero values eg.
#,##0.00;[Red](#,##0.00);
(colon ( added at the end.

[Just Shan]

This was one option I already knew.

I was looking forward to edit the code, which makes it easy to change All #Div/0! errors at once.

Hope it's possible.

Best Regards,

S h a n