Convert Named Ranges to Addresses
 

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

Convert Named Ranges to Addresses
 

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

Convert Named Ranges to Addresses
 

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

Convert Named Ranges to Addresses
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Pflugs" wrote in message
...
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

Convert Named Ranges to Addresses
 

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

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Pflugs" wrote in message
...
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