Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
union of named ranges based only on the names of those ranges | Excel Programming | |||
Copy data in named ranges to a newer version of the same template to identical ranges | Excel Programming | |||
Absolute addresses and ranges | Excel Programming | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
named ranges - changing ranges with month selected | Excel Programming |