Convert Named Ranges to Addresses

P

Pflugs

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
 
P

Pflugs

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
 
J

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
 
P

Pflugs

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top