ExcelBanter - catching "#DIV/0!" in a loop

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - catching "#DIV/0!" in a loop (https://www.excelbanter.com/excel-programming/389642-catching-div-0-loop.html)

catching "#DIV/0!" in a loop

Hello,

I am running the following to replace "alt+Enter". Whenever I run across a
cell with the value of "#DIV/0!" I get an error (Error = 2007). What I
don't understand is that the "#DIV/0!" is not a equation, but a value. Any
help with this would be appreciated.

Thanks, sck10

Range("rng_test").Select
Dim myCell As Range
'For Each myCell In Range("rng_template").Cells
For Each myCell In Selection.Cells
Range("rng_value").Value = myCell.Value

End If
If myCell.Value = "#DIV/0!" Then
myCell.Value = ""
End If

myCell.Value = Replace(myCell.Value, Chr(10), " ")
myCell.Value = Replace(myCell.Value, Chr(13), " ")
Next

catching "#DIV/0!" in a loop

Forget the loop, just use

Range("rng_test").Replace Chr(10), " "
Range("rng_test").Replace Chr(13), " "

HTH,
Bernie
MS Excel MVP

"sck10" wrote in message ...
Hello,

I am running the following to replace "alt+Enter". Whenever I run across a cell with the value of
"#DIV/0!" I get an error (Error = 2007). What I don't understand is that the "#DIV/0!" is not a
equation, but a value. Any help with this would be appreciated.

Thanks, sck10

Range("rng_test").Select
Dim myCell As Range
'For Each myCell In Range("rng_template").Cells
For Each myCell In Selection.Cells
Range("rng_value").Value = myCell.Value

End If
If myCell.Value = "#DIV/0!" Then
myCell.Value = ""
End If

myCell.Value = Replace(myCell.Value, Chr(10), " ")
myCell.Value = Replace(myCell.Value, Chr(13), " ")
Next

catching "#DIV/0!" in a loop

Not sure what you are specifically asking, but here is one approach.

Range("rng_test").Select
Dim myCell As Range
'For Each myCell In Range("rng_template").Cells
For Each myCell In Selection.Cells
Range("rng_value").Value = myCell.Value

End If
If myCell.Text = "#DIV/0!" Then
myCell.Value = ""
End If

myCell.Value = Replace(myCell.Value, Chr(10), " ")
myCell.Value = Replace(myCell.Value, Chr(13), " ")
Next

You can also do

If myCell.Value = cvErr(xlErrDiv0) Then
myCell.Value = ""
End If

or for any error
If iserror(myCell.Value) Then
myCell.Value = ""
End If

and a little demo from the immediate window:

? activecell.Text
#DIV/0!
? activecell.Value = cvErr(xlErrDiv0)
True

--
Regards,
Tom Ogilvy

"sck10" wrote:

Hello,

I am running the following to replace "alt+Enter". Whenever I run across a
cell with the value of "#DIV/0!" I get an error (Error = 2007). What I
don't understand is that the "#DIV/0!" is not a equation, but a value. Any
help with this would be appreciated.

Thanks, sck10

Range("rng_test").Select
Dim myCell As Range
'For Each myCell In Range("rng_template").Cells
For Each myCell In Selection.Cells
Range("rng_value").Value = myCell.Value

End If
If myCell.Value = "#DIV/0!" Then
myCell.Value = ""
End If

myCell.Value = Replace(myCell.Value, Chr(10), " ")
myCell.Value = Replace(myCell.Value, Chr(13), " ")
Next

catching "#DIV/0!" in a loop

Thanks Tom,

Appreciate the code...

sck10

"Tom Ogilvy" wrote in message
...
Not sure what you are specifically asking, but here is one approach.

Range("rng_test").Select
Dim myCell As Range
'For Each myCell In Range("rng_template").Cells
For Each myCell In Selection.Cells
Range("rng_value").Value = myCell.Value

End If
If myCell.Text = "#DIV/0!" Then
myCell.Value = ""
End If

myCell.Value = Replace(myCell.Value, Chr(10), " ")
myCell.Value = Replace(myCell.Value, Chr(13), " ")
Next

You can also do

If myCell.Value = cvErr(xlErrDiv0) Then
myCell.Value = ""
End If

or for any error
If iserror(myCell.Value) Then
myCell.Value = ""
End If

and a little demo from the immediate window:

? activecell.Text
#DIV/0!
? activecell.Value = cvErr(xlErrDiv0)
True

--
Regards,
Tom Ogilvy

"sck10" wrote:

Hello,

I am running the following to replace "alt+Enter". Whenever I run across
a
cell with the value of "#DIV/0!" I get an error (Error = 2007). What I
don't understand is that the "#DIV/0!" is not a equation, but a value.
Any
help with this would be appreciated.

Thanks, sck10

Range("rng_test").Select
Dim myCell As Range
'For Each myCell In Range("rng_template").Cells
For Each myCell In Selection.Cells
Range("rng_value").Value = myCell.Value

End If
If myCell.Value = "#DIV/0!" Then
myCell.Value = ""
End If

myCell.Value = Replace(myCell.Value, Chr(10), " ")
myCell.Value = Replace(myCell.Value, Chr(13), " ")
Next

catching "#DIV/0!" in a loop

Thank you Bernie,

This helps greatly...

sck10

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Forget the loop, just use

Range("rng_test").Replace Chr(10), " "
Range("rng_test").Replace Chr(13), " "

HTH,
Bernie
MS Excel MVP

"sck10" wrote in message
...
Hello,

I am running the following to replace "alt+Enter". Whenever I run across
a cell with the value of "#DIV/0!" I get an error (Error = 2007). What
I don't understand is that the "#DIV/0!" is not a equation, but a value.
Any help with this would be appreciated.

Thanks, sck10

Range("rng_test").Select
Dim myCell As Range
'For Each myCell In Range("rng_template").Cells
For Each myCell In Selection.Cells
Range("rng_value").Value = myCell.Value

End If
If myCell.Value = "#DIV/0!" Then
myCell.Value = ""
End If

myCell.Value = Replace(myCell.Value, Chr(10), " ")
myCell.Value = Replace(myCell.Value, Chr(13), " ")
Next