Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Defined names loop
Hi all,
How to loop through range names? range names like menu Insert| name | define similar to intention of (invalid) pseudocode below For each range in Worksheet.Ranges Debug.Print range.name next since i don't find a ranges collection the above won't work the reason i ask is i had named some ranges inconsistently and wanted to run a quick macro to fix them eg labor00 labor2001 .... should be labor2000 labor2001 ... thanks mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Defined names loop
Forget using a macro.... download Jan Karel Pieterse's NameManager Add-In...
it will allow you to do lots of things with Defined Names. After you install it, it will be available in the Tools item on the Menu Bar. To rename a Defined Name, just double click it in the NameManager list. http://www.oaltd.co.uk/DLCount/DLCou...ameManager.zip -- Rick (MVP - Excel) "mp" wrote in message ... Hi all, How to loop through range names? range names like menu Insert| name | define similar to intention of (invalid) pseudocode below For each range in Worksheet.Ranges Debug.Print range.name next since i don't find a ranges collection the above won't work the reason i ask is i had named some ranges inconsistently and wanted to run a quick macro to fix them eg labor00 labor2001 ... should be labor2000 labor2001 ... thanks mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Defined names loop
However, if you wish to do this the "macro" way, this is how you would
iterate through the Names collection... Dim N As Name For Each N In Application.Names Debug.Print N.Name & " == " & N.RefersTo Next Just address the appropriate properties of each iterated name as needed. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Forget using a macro.... download Jan Karel Pieterse's NameManager Add-In... it will allow you to do lots of things with Defined Names. After you install it, it will be available in the Tools item on the Menu Bar. To rename a Defined Name, just double click it in the NameManager list. http://www.oaltd.co.uk/DLCount/DLCou...ameManager.zip -- Rick (MVP - Excel) "mp" wrote in message ... Hi all, How to loop through range names? range names like menu Insert| name | define similar to intention of (invalid) pseudocode below For each range in Worksheet.Ranges Debug.Print range.name next since i don't find a ranges collection the above won't work the reason i ask is i had named some ranges inconsistently and wanted to run a quick macro to fix them eg labor00 labor2001 ... should be labor2000 labor2001 ... thanks mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Defined names loop
Thanks will also check the addin
"Rick Rothstein" wrote in message ... However, if you wish to do this the "macro" way, this is how you would iterate through the Names collection... Dim N As Name For Each N In Application.Names Debug.Print N.Name & " == " & N.RefersTo Next Just address the appropriate properties of each iterated name as needed. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Forget using a macro.... download Jan Karel Pieterse's NameManager Add-In... it will allow you to do lots of things with Defined Names. After you install it, it will be available in the Tools item on the Menu Bar. To rename a Defined Name, just double click it in the NameManager list. http://www.oaltd.co.uk/DLCount/DLCou...ameManager.zip -- Rick (MVP - Excel) "mp" wrote in message ... Hi all, How to loop through range names? range names like menu Insert| name | define similar to intention of (invalid) pseudocode below For each range in Worksheet.Ranges Debug.Print range.name next since i don't find a ranges collection the above won't work the reason i ask is i had named some ranges inconsistently and wanted to run a quick macro to fix them eg labor00 labor2001 ... should be labor2000 labor2001 ... thanks mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Defined names loop
its definitely not a good idea to use keywords for variables. yuo use range
as a Range object... dim cell as range then use the variable called cell to check a "name" you could use debug.print Range("A1").Name.Name this will raise an error if there is no name, so for each cell in Selection on error resume next debug.print cell.Address(False,False), Cell.Name.Name on error goto 0 next "mp" wrote: Hi all, How to loop through range names? range names like menu Insert| name | define similar to intention of (invalid) pseudocode below For each range in Worksheet.Ranges Debug.Print range.name next since i don't find a ranges collection the above won't work the reason i ask is i had named some ranges inconsistently and wanted to run a quick macro to fix them eg labor00 labor2001 .... should be labor2000 labor2001 ... thanks mark |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Defined names loop
"Patrick Molloy" wrote in message ... its definitely not a good idea to use keywords for variables. yuo use range as a Range object... absolutely, that was just pseudocode to show the idea for each <rangeobject in <rangesobject dim cell as range then use the variable called cell to check a "name" you could use debug.print Range("A1").Name.Name this will raise an error if there is no name, so for each cell in Selection on error resume next debug.print cell.Address(False,False), Cell.Name.Name on error goto 0 next thanks i'll give it a try mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defined Range Names in formulas | Excel Discussion (Misc queries) | |||
User defined range for vlookup w/ loop | Excel Programming | |||
Defining a range using defined names | Excel Programming | |||
Rename Several Defined Range Names with Macro | Excel Programming | |||
Listing or Changing all Defined Range Names (using VBA) | Excel Programming |