DIV/0 ERROR - How eliminate them in many cells contemporaneously

[Metallo]

Hi,

Normally, I use the formula (EXAMPLE):
=IF(ISERROR(G12/G11*1000),"",G12/G11*1000)

In this way I prevent the error to appear.

The problem is that somebody sent me a very big Excel file with plenty
of DIV Errors, therefore I am asking how I can apply my formula to all
of them in one go.
Certainly, I cannot do this cell by cell and I am sure there is a
"batch" process I can use.

Thanks for your help

Alex

 

[Papou]

Hi
Try something like this (there surely is something better) :
Sub AddErrorTrap()
Dim UsdRng, Cel As Range, OldFormula$, NewFormulaPart1$, NewFormula$
NewFormulaPart1 = "=IF(ISERROR("
Set UsdRng = ActiveSheet.UsedRange
For Each Cel In UsdRng
If Cel.HasFormula Then
OldFormula = Right(Cel.Formula, Len(Cel.Formula) - 1)
NewFormula = NewFormulaPart1 & OldFormula & "),""""," & OldFormula & ")"
Cel.Formula = NewFormula
End If
Next Cel
End Sub

HTH
Regards
Pascal

 

[Cecilkumara Fernando]

Metallo,
On a copy of your very big Excel file run this macro
Sub pp()
Application.Calculation = xlCalculationManual
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula And _
Mid(cell.Formula, 2, 10) <> "IF(ISERROR" Then
currformula = Mid(cell.Formula, 2, 255)
newformula = "=if(iserror(" & currformula & _
"),""""," & currformula & ")"
With cell
..Formula = newformula
End With
End If
Next
Application.Calculation = xlCalculationAutomatic
End Sub

HTH,
Cecil

 

[Metallo]

Hi,

Thanks Pascal.

However, if anybody as got another solution, it's very welcome.

Thank you
Alex

 

[Metallo]

Hi,

Thanks Pascal.

However, if anybody as got another solution, it's very welcom.

Thank you
Alex

 

[JWolf]

Try this, it only appends the if(iserror to cells with div/0! errors:
Sub test()
Dim cell As Range
Dim div0formula As String
For Each cell In ActiveSheet.UsedRange
If IsError(cell.Value) Then
If cell.Value = CVErr(xlErrDiv0) Then
div0formula = Mid(cell.Formula, 2)
cell.Formula = "=IF(ISERROR(" & div0formula & "),"""","
& div0formula & ")"
End If
End If
Next cell
End Sub

 

[Doria/Warris]

Fernando,

Thanks for your suggestion.
May I ask you to be a bit more specific, I am not familiar with this kind of
things in Excel.

For instance, what do you mean by Sub pp()?
Thanks

Metallo

 

[JWolf]

This is a VBA macro, the name of the macro is PP.
Here is a different VBA macro, updating the previous one I provided.
It will run much faster on large amounts of data.

Sub repldivzero()
Application.ScreenUpdating = False
Dim rowcount As Long
Dim colcount As Integer
Dim i As Long
Dim j As Integer
Dim trange As Range
Dim temparray
Dim cell As Range
Dim div0formula As String
rowcount = ActiveSheet.UsedRange.Rows.Count
colcount = ActiveSheet.UsedRange.Columns.Count
ReDim temparray(rowcount, colcount)
For i = 1 To rowcount
For j = 1 To colcount
Set cell = Cells(i, j)
temparray(i, j) = cell.Formula
If IsError(cell.Value) Then
If cell.Value = CVErr(xlErrDiv0) Then
div0formula = Mid(cell.Formula, 2)
temparray(i, j) = "=IF(ISERROR(" & div0formula &
"),""""," & div0formula & ")"
End If
End If
Next j
Next i
ActiveSheet.UsedRange.Formula = temparray
Application.ScreenUpdating = True
End Sub

To use this sub (repldivzero) you must do the following:
Copy the lines from Sub to End Sub,
Go to Excel
Hit Alt+F11 to go to the Visual Basic Editor
On the top menu bar do: insert-->module
Paste the clipboard contents into the module window.
Hit Alt+F11 to return to Excel.
Select the tab you want to run the macro on, i.e. make it the active sheet
On main menu bar: tools-->macro-->macros
highlight repldivzero and click Run.

The macro repldivzero will then replace the formula in any cell of the
activesheet which results in #DIV/0! with a new formula of the form:
=IF(ISERROR(original formula),"",originalformula)

 

[JWolf]

One other thing I forgot in this message:
On the first line of your VBA module above where the Sub repldivzero is
pasted,
Type in the following: Option Base 1

 

[Doria/Warris]

Thank you JWolf.

I will look into this and let you know if I succeeded.

Cheers
Metallo

 

[Metallo]

Hi JWolf,

When I run the macro, the following error pops up:

Compile error:
expected: Line number, or label or statement or end of statement

This is what I paste:

Option Base 1
Sub repldivzero()
Application.ScreenUpdating = False
Dim rowcount As Long
Dim colcount As Integer
Dim i As Long
Dim j As Integer
Dim trange As Range
Dim temparray
Dim cell As Range
Dim div0formula As String
rowcount = ActiveSheet.UsedRange.Rows.Count
colcount = ActiveSheet.UsedRange.Columns.Count
ReDim temparray(rowcount, colcount)
For i = 1 To rowcount
For j = 1 To colcount
Set cell = Cells(i, j)
temparray(i, j) = cell.Formula
If IsError(cell.Value) Then
If cell.Value = CVErr(xlErrDiv0) Then
div0formula = Mid(cell.Formula, 2)
temparray(i, j) = "=IF(ISERROR(" & div0formula &
"),""""," & div0formula & ")"
End If
End If
Next j
Next i
ActiveSheet.UsedRange.Formula = temparray
Application.ScreenUpdating = True
End Sub

I'm sure you see where the issue is.

Thanks again for your help

Metallo

 

[GF6]

On the line which begins temparray(i,j)= the copy paste inserted a line
break. Either go to the end of the line and hit the delete key until it
is all one line or after the last ampersand hit space and underscore (
_) to create a line continuation.