renaming named ranges
 

Dear Experts:

I got a workbook with a lot of named ranges. They are named range1,
range2, range3, range4 .....range40.

Is it possible to ADD a ZERO before the SINGLE digit named ranges
(using VBA) so that they look like as follows: range01, range02, ...
range09 ... . Ranges named range10, range11 .... range40 etc. are to
be left untouched.

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas

renaming named ranges
 

Hi Andreas,

Am Mon, 4 Apr 2011 09:34:24 -0700 (PDT) schrieb AndreasHermle:

Is it possible to ADD a ZERO before the SINGLE digit named ranges
(using VBA) so that they look like as follows: range01, range02, ...
range09 ... . Ranges named range10, range11 .... range40 etc. are to
be left untouched.

Sub RangeNames()
Dim rngName As Name

For Each rngName In ThisWorkbook.Names
If Len(rngName.Name) = 6 Then
rngName.Name = Left(rngName.Name, 5) & _
Format(Right(rngName.Name, 1), "00")
End If
Next
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

renaming named ranges
 

I got a workbook with a lot of named ranges. They are named
range1, range2, range3, range4 .....range40.

Is it possible to ADD a ZERO before the SINGLE digit named
ranges (using VBA) so that they look like as follows: range01,
range02, ...range09 ... . Ranges named range10, range11 ....
range40 etc. are to be left untouched.

I would do it like this...

Sub FillOutRangeNames()
Dim N As Name
For Each N In ThisWorkbook.Names
If UCase(N.Name) Like "RANGE#" Then
N.Name = "Range" & Format(Right(N.Name, 1), "00")
End If
Next
End Sub

Rick Rothstein (MVP - Excel)

renaming named ranges
 

On Apr 4, 8:36*pm, Claus Busch wrote:
Hi Andreas,

Am Mon, 4 Apr 2011 09:34:24 -0700 (PDT) schrieb AndreasHermle:

Is it possible to ADD a ZERO before the SINGLE digit named ranges
(using VBA) so that they look like as follows: range01, range02, ...
range09 ... . Ranges named range10, range11 .... range40 etc. are to
be left untouched.

Sub RangeNames()
Dim rngName As Name

For Each rngName In ThisWorkbook.Names
* * If Len(rngName.Name) = 6 Then
* * * * rngName.Name = Left(rngName.Name, 5) & _
* * * * * * *Format(Right(rngName.Name, 1), "00")
* * End If
Next
End Sub

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,

great, works like a charm. Thank you very much for your great and
professional support. Regards, Andreas

renaming named ranges
 

On Apr 4, 8:53*pm, "Rick Rothstein"
wrote:
I got a workbook *with a *lot of named ranges. They are named
range1, range2, range3, range4 .....range40.

Is it possible to ADD a ZERO before the SINGLE digit named
ranges (using VBA) so that they look like as follows: range01,
range02, ...range09 ... . Ranges named range10, range11 ....
range40 etc. are to be left untouched.

I would do it like this...

Sub FillOutRangeNames()
* Dim N As Name
* For Each N In ThisWorkbook.Names
* * If UCase(N.Name) Like "RANGE#" Then
* * * N.Name = "Range" & Format(Right(N.Name, 1), "00")
* * End If
* Next
End Sub

Rick Rothstein (MVP - Excel)

Hi Rick,

as always, your codes work just fine. I really appreciate your
professional support.

Thank you very much.

Regards, Andreas