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