Convert Named Ranges to Addresses

[Guest]

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

 

[Jon Peltier]

Use 'nme.RefersToRange.Address'.

- Jon

 

[Guest]

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]

Oh, you want to convert the references to the names to their cell addresses
in the direct dependents. That's why the code was so intricate.

To prevent some difficulties, change this:

ActiveSheet.Name & "!"

to this:

"'" & ActiveSheet.Name & "'!"

I'm too bogged down at the moment to help with the rest.

- Jon

 

[Guest]

Not a problem. I was just interested to see if anyone had encountered this
before. I think I will have to write my own REPLACE function that looks for
whole words only before substituting.

Thanks for checking,
Pflugs