That helps me get the address, but it still doesn't solve the problem of the
best way to find and replace the reference within a formula. I also have a
few formulae that use named ranges within an array function, so this is a
challenging problem.

Thanks,
Pflugs

"Jon Peltier" wrote:

Use 'nme.RefersToRange.Address'.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"Pflugs" wrote in message
...
Hi,

I have about two dozen named ranges that I'd like to convert to their
addresses. I quickly wrote the code below, and I know that it is not
optimized and contains debugging code.

====================================
Sub replaceNamedRanges()

Dim c As Range, nme As Name, ws As Worksheet, addrss As String
Dim wb As Workbook

Set wb = Workbooks("Trilateration Template.xls")

' Worksheet level
For Each ws In wb.Sheets
Debug.Print ws.Name
ws.Activate
For Each nme In ws.Names
Debug.Print nme.Name
Range(nme).DirectDependents.Select
For Each c In Range(nme).DirectDependents
c.Select
Debug.Print c.Address
addrss = Replace(nme.RefersTo, "=", "")
addrss = Replace(addrss, ActiveSheet.Name & "!", "")
shortname = Replace(nme.Name, ActiveSheet.Name & "!", "")
c.Formula = Replace(c.Formula, shortname, addrss)
Next
nme.Delete
Next
Next

' Workbook level
For Each nme In wb.Names
Debug.Print nme.Name
For Each c In Range(nme).DirectDependents
a = Replace(nme.RefersTo, "=", "")
a = Replace(a, ActiveSheet.Name & "!", "")
addrss = Replace(nme.RefersTo, "=", "")
addrss = Replace(addrss, ActiveSheet.Name & "!", "")
shortname = Replace(nme.Name, ActiveSheet.Name & "!", "")
c.Formula = Replace(c.Formula, shortname, addrss)
Next
nme.Delete
Next

End Sub
==============================================

The code doesn't work in situations when the named range is something like
"i" and the cell's formula contains a reference to "I15". Can anyone
suggest
a better method or workaround? I tried using JKP's Named Range Manager,
and
though it was a terrific tool, it doesn't have code for converting named
ranges.

Thanks,
Pflugs