Curious string error in named range automation attempt - just doesn'tseem to make sense!

[Mark Stephens]

Hi there,

This is a strange one ...

I have a requirement to name a fairly large range of cells and so wish to automate the process.

As I usually do I recorded a macro to see how it works and came up with the following code which name the required range no problem:

ActiveWorkbook.Names.Add Name:="May_13_GBP", RefersToR1C1:= "='BY MONTH'!R24C2"

In order automate it I need to pick up the name of the range from another two cells by concatenating their contents so I came up with the following code:


sName = Format(Cells(22, b + 1), "mmm yy") & "_" & Range("A24").Value
ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:="='BY MONTH'!R24C2"

The string in the second example is identical to the string in the first, so why wouldn't it work???

I am at a loss it just doesn't make any sense, it should work fine.

Anyone have any idea what I am missing here?

Thanks and regards, Mark

 

[Auric__]

This is a strange one ...

I have a requirement to name a fairly large range of cells and so wish
to automate the process.

As I usually do I recorded a macro to see how it works and came up with
the following code which name the required range no problem:

ActiveWorkbook.Names.Add Name:="May_13_GBP", RefersToR1C1:= "='BY
MONTH'!R24C2"

In order automate it I need to pick up the name of the range from
another two cells by concatenating their contents so I came up with the
following code:


sName = Format(Cells(22, b + 1), "mmm yy") & "_" & Range("A24").Value
ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:="='BY MONTH'!R24C2"

The string in the second example is identical to the string in the
first, so why wouldn't it work???

I am at a loss it just doesn't make any sense, it should work fine.

Anyone have any idea what I am missing here?

The two example strings are *not* identical. Formatting #May 2013# with the
string "mmm yy" gives you "May 13" -- note the space, the source of your
woes. Try changing your format string to "mmm_yy".

 

[Mark Stephens]

Hi there,



This is a strange one ...



I have a requirement to name a fairly large range of cells and so wish to automate the process.



As I usually do I recorded a macro to see how it works and came up with the following code which name the required range no problem:



ActiveWorkbook.Names.Add Name:="May_13_GBP", RefersToR1C1:= "='BY MONTH'!R24C2"



In order automate it I need to pick up the name of the range from another two cells by concatenating their contents so I came up with the following code:





sName = Format(Cells(22, b + 1), "mmm yy") & "_" & Range("A24").Value

ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:="='BY MONTH'!R24C2"



The string in the second example is identical to the string in the first, so why wouldn't it work???



I am at a loss it just doesn't make any sense, it should work fine.



Anyone have any idea what I am missing here?



Thanks and regards, Mark

Hi there Auric,

Thank you very much for the help, much appreciated, kind regards, Mark

 

[GS]

Firstly.., I assume you have an *extremely good reason* for
*deliberately* assigning a global scope (workbook level) name to your
range. Otherwise, in general, this is not considered 'good practice'.

That said, I recommend modifying your automated process as follows...

Dim sName$
sName = Format(Cells(22, b + 1), "mmm_yy") & "_" & Range("A24").Value
With ActiveSheet
.Names.Add Name:="'" & .Name & "'!" & sName, _
RefersToR1C1:="='BY MONTH'!R24C2"
End With 'ActiveSheet

...which assumes the active sheet may not be named "BY MONTH" since you
have hard-coded the sheet ref for RefersTo! Otherwise...

With ActiveSheet
.Names.Add Name:="'" & .Name & "'!" & sName, RefersToR1C1:="=R24C2"
End With 'ActiveSheet

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Mark Stephens]

Hi there,



This is a strange one ...



I have a requirement to name a fairly large range of cells and so wish to automate the process.



As I usually do I recorded a macro to see how it works and came up with the following code which name the required range no problem:



ActiveWorkbook.Names.Add Name:="May_13_GBP", RefersToR1C1:= "='BY MONTH'!R24C2"



In order automate it I need to pick up the name of the range from another two cells by concatenating their contents so I came up with the following code:





sName = Format(Cells(22, b + 1), "mmm yy") & "_" & Range("A24").Value

ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:="='BY MONTH'!R24C2"



The string in the second example is identical to the string in the first, so why wouldn't it work???



I am at a loss it just doesn't make any sense, it should work fine.



Anyone have any idea what I am missing here?



Thanks and regards, Mark

Hi Gary,

Thanks for that, yes there is a good reason, the reference needs to be used globally by every sheet in the workbook.

Thanks for the input, M.

 

[GS]

Thanks for that, yes there is a good reason, the reference needs to

be used globally by every sheet in the workbook.

You can ref a local scope name from any sheet...

='BY Month'!May_13_GBP

...by simply including the sheetname the local name is defined on. Thus,
your reason is not a good one!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion