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

"ker_01" wrote:

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