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

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

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

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


"Metallo" a écrit dans le message de news:
...
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

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

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" wrote in message
om...
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

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

Hi,

Thanks Pascal.

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

Thank you
Alex

"Papou" wrote in message ...
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


"Metallo" a écrit dans le message de news:
...
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

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

Hi,

Thanks Pascal.

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

Thank you
Alex

"Papou" wrote in message ...
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


"Metallo" a écrit dans le message de news:
...
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

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

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

Metallo wrote:
Hi,

Thanks Pascal.

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

Thank you
Alex

"Papou" wrote in message ...

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


"Metallo" a écrit dans le message de news:
. ..

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

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

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
"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...
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" wrote in message
om...
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

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

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)




Doria/Warris wrote:
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
"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...

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" wrote in message
.com...

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

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

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

JWolf wrote:

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)




Doria/Warris wrote:

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
"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...

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" wrote in message
om...

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

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

Thank you JWolf.

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

Cheers
Metallo
"JWolf" wrote in message
.. .
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

JWolf wrote:

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)




Doria/Warris wrote:

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
"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...

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" wrote in message
om...

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

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

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


JWolf wrote in message ...
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

JWolf wrote:

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)




Doria/Warris wrote:

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
"Cecilkumara Fernando" <cekufdo@sltnetDOTlk wrote in message
...

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" wrote in message
om...

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

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

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.

Metallo wrote:
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