Returning a named range source reference

[ker_01]

Using XL03

I am using the code below, trying to create a list of named ranges and their
source references. I'm having two problems.

(1) There are some other names (named objects) in the workbook, and I don't
want to return those; I just want to return named ranges, and

(2) The code below returns the resulting range referred to, but not the
source reference. For example, if the named range is "=offset(A1,1,1)" then I
need to see that offset statement, not the resulting range of "B2". I tried
everything that made sense from the help object model under names, and I've
tried various guesses beyond that (.source, etc) but haven't lucked across
the right keyword.

Any help would be greatly appreciated!
Keith

Sub MakeNRList()

Dim n As Name
On Error Resume Next
For Each n In ThisWorkbook.Names
i = i + 1
Sheet3.Range("A" & i) = n.Name
Sheet3.Range("B" & i) = n.RefersToRange.Address(False, False) '<--
Next n

End Sub

 

[Barb Reinhardt]

Try this

On Error Resume Next
Sheet3.Range("B" & i) = n.RefersToRange.Address(False, False)
If Err <> 0 Then
Application.GoTo Name.Name
Sheet3.Range("B"&I) =Selection.Address
End If
On Error GoTo 0

 

[ker_01]

Barb-

Thank you for the quick response.

The n.RefersToRange.Address(False, False) is giving me the resulting range
(e.g., "B1" from the original post) and not the original formula as seen in
the name manager.

I've tinkered with the inner loop as the compiler didn't like the
"Name.Name" but I think it was intended to just go to that range. Updated
code pasted below.

With the updated code, all of my named ranges have a name and
referstoaddress; about half of the remaining names have a referstoaddress and
the other half have a selection.address. I was trying to exclude the names
that weren't ranges just for convenience (I counted, and I have over 300
named ranges, and hundreds of other names as well). The bigger problem is
still finding a way to extract the original named range formula, as I have to
validate that all 300 are accurate before giving this workbook to my internal
customer, as errors will be very difficult to detect just from looking at the
output data.
Thanks!
Keith

Revised code:
-------------------------
Sub MakeNRList()
Dim n As Name

On Error Resume Next

For Each n In ThisWorkbook.Names
i = i + 1
Sheet3.Range("G" & i) = n.Name
Sheet3.Range("H" & i) = n.RefersToRange.Address(False, False)
If Err <> 0 Then
Application.Goto Name '.Address '.Name
Sheet3.Range("J" & i) = Selection.Address
End If
Next n

On Error GoTo 0

End Sub



Many thanks,
Keith

 

[ker_01]

Ugh-

Turns out, what I need is .RefersTo

I had tried that before but only seen values (such as when using .value), so
I kept testing. Turns out that the original formula is there, but when my
code pasted it into the cell it evaluates, and then I was seeing the number
and not the original formula. I added a single quote, and now everything is
working as expected.

Thanks again,
Keith