If your names all point to single cell ranges, you could use:

if iserror(nm.referstorange.value) then

I misunderstood your original post--I thought the name itself was wrong.

Jim May wrote:

Thanks Dave,,
The Refersto string is returning

=Sheet1!$C$12

using the Instr() to look for the #REF! wont do.
But Instr() would work if I could convert the =Sheet1!$C$12 to it's value -
which is either 123, 234, 345, #REF! or 456.
How could I do this?

"Dave Peterson" wrote in message
...
Maybe you could use instr() to look for the #ref! error string in the
refers to
string--just in case there's other stuff in that string.

And if I were checking a cell for a possible error, I'd use:

if iserror(mycell.value) then
...

You can use VBA's iserror function instead of excel's worksheet function.

Jim May wrote:

I have five cells named Sub1, Sub2,, Sub5
My Sub4 cell currently shows #REF! since
its source row was deleted. I'm trying to copy
all names over to sheet2 (where I want to Sum() all 5 values and
naturally
replace the #REF! with
a 0 (zero) but as I step through the code the If line
is never recognizing the "#REF!" - Can someone
help?

Sub tester()
i = 1
For Each nm In ActiveWorkbook.Names
Sheet2.Cells(i, 1).Value = nm.Name
If nm.RefersTo = "#REF!" Then
Sheet2.Cells(i, 2).Value = 0
Else
Sheet2.Cells(i, 2).Value = nm.RefersTo
End If
i = i + 1
Next
End Sub

--

Dave Peterson

--

Dave Peterson