"#REF!" not being picked up

  • Thread starter Thread starter Jim May
  • Start date Start date
J

Jim May

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
 
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.
 
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?
 
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.
 
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?
 
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 said:
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?
Click to expand...
 
Got It !!
Tks,
Jim


Dave Peterson said:
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
...
Click to expand...
 
Back
Top