"#REF!" not being picked up
 

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

"#REF!" not being picked up
 

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

"#REF!" not being picked up
 

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

"#REF!" not being picked up
 

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

"#REF!" not being picked up
 

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

"#REF!" not being picked up
 

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

"#REF!" not being picked up
 

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

"#REF!" not being picked up
 

Got It !!
Tks,
Jim


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