Prev: OO4O execute a query against Oracle, VBA Excel 2003
Next: External Data
From: mp on 22 Sep 2009 23:41
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


From: Rick Rothstein on 23 Sep 2009 03:45
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/DLCount.asp?file=NameManager.zip

--
Rick (MVP - Excel)


"mp" <nospam(a)Thanks.com> wrote in message
news:%23bSnt%23$OKHA.508(a)TK2MSFTNGP06.phx.gbl...
> 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
>

From: Rick Rothstein on 23 Sep 2009 03:49
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" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message
news:OKfv%23GCPKHA.508(a)TK2MSFTNGP06.phx.gbl...
> 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/DLCount.asp?file=NameManager.zip
>
> --
> Rick (MVP - Excel)
>
>
> "mp" <nospam(a)Thanks.com> wrote in message
> news:%23bSnt%23$OKHA.508(a)TK2MSFTNGP06.phx.gbl...
>> 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
>>
>

From: Patrick Molloy on 23 Sep 2009 04:52
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
>
>
>
From: mp on 23 Sep 2009 12:11

"Patrick Molloy" <PatrickMolloy(a)discussions.microsoft.com> wrote in message
news:E8EC5EBA-5089-4641-ACEA-4F3C496E2BF9(a)microsoft.com...
> 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


 |  Next  |  Last
Pages: 1 2
Prev: OO4O execute a query against Oracle, VBA Excel 2003
Next: External Data