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

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

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

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

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

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