#DIV/0! replace error message with the number 0.

I am trying to help a colleague who has a workbook with 130 tabs.

She wants to set a rule to display 0 instead of #DIV/0 when ever there is division by 0. this rule should apply to all pages and cells.

It would be like the auto replace function in word that changes a dash into the longer dash.

I have seen how to use IFERROR is functions, but hse does not want to rework 130 tabs worth of calculations.

Any suggestions?

Using VBA to loop through 130 tabs is an interesting idea. Might you be able to show a sample of this? I have not seen the workbook so I do not know the complexity of the formulas. Additionally, I don't even know if the formulas are the same on each sheet.

I'm guessing that each field would have to be looked at to see if its a formula and modify it to something like: ISERROR([formula],0)

since there are 1047576xXFD cells on each tab and 130 tabs, this would be no easy task and probably take quite a long time to process.

I did find a pile of VB code that allows me to replace errors with a custom message. This takes away the formula which produced an error and replaces it with text. The problem is that the values in the workbook change on a regular basis and I need to keep the formulas.


Sub ReplaceErrors()
'Update 20131216
Dim Rng As Range
Dim WorkRng As Range
Dim ReplaceStr As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
ReplaceStr = Application.InputBox("Replace text", xTitleId, Type:=2)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas, 16)
WorkRng.Value = ReplaceStr
End Sub



Maybe this code could be reworked to loop through the tabs and reformat cells displaying error messages with the ISERROR() function.

what do you think?

The code should work regardless of the exact structure.

Open excel. Create 130 worksheets in a workbook. On each worksheet generate a few randomly placed formulas that divide by 0.

The VBA should loop through the worksheets. For each worksheet select the formulas that divide by 0 and modify them to output 0 rather than #DIV/0!

I just havent used VB to loop through all the cells on all the worksheets in a workbook. I'm surely no VBA expert.