Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Eliminate ALL cells containing the same data | Excel Discussion (Misc queries) | |||
Modify to eliminate #NUM! error | Excel Worksheet Functions | |||
Eliminate zero value cells in an EXCEL chart | Charts and Charting in Excel | |||
Eliminate matching cells | Excel Discussion (Misc queries) | |||
How do I eliminate, in Excel XP, "too many cell formats" error? | Excel Discussion (Misc queries) |