| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Question on determining "ROW" inside of a "For .. RANGE " loop | Excel Programming | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Catching "no cells were found" | Excel Programming | |||
| LOOP BETWEEN "FRONT" AND "END" SHEETS? | Excel Programming | |||
Linear Mode
