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" <(E-Mail Removed)> wrote in message
news:90B28D6D-C2B4-48E2-99B2-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:8A7EA832-94EC-45D3-B82C-(E-Mail Removed)...
>> > 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
>>
>>
>>
Similar Threads
