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

Ok, I got it -- Sorry (but there is maybe a better way --??)

Sub tester2()
i = 1
For Each nm In ActiveWorkbook.Names
Sheet2.Cells(i, 1).Value = nm.Name
Sheet2.Cells(i, 2).Value = nm.RefersTo
If Application.WorksheetFunction.IsError(Sheet2.Cells (i, 2)) Then
Sheet2.Cells(i, 2).Value = 0
End If
i = i + 1
Next
End Sub
"Jim May" wrote in message
...
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

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

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

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

Stepping thru (Using F8) on the 4th loop @ cell = #REF! on my line:

If InStr(nm.RefersToRange, "Error 2023") Then

it results in Type Mismatch -- any ideas?


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

If you're looking for that error in the range that the name refers to, then use:
if iserror(nm.referstorange.value) then

if you're looking for an error in the formula that the name refers to, then use
instr().

if instr(1, nm.refersto, "#ref!", vbtextcompare) 0 then
...


Jim May wrote:

Stepping thru (Using F8) on the 4th loop @ cell = #REF! on my line:

If InStr(nm.RefersToRange, "Error 2023") Then

it results in Type Mismatch -- any ideas?

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

--

Dave Peterson