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

Discussion in 'Excel Programming' started by Mark Stephens, Apr 8, 2014.

  1. 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
     
    Mark Stephens, Apr 8, 2014
    #1
    1. Advertisements

  2. Mark Stephens

    Auric__ Guest

    Re: Curious string error in named range automation attempt - just doesn't seem to make sense!

    Mark Stephens wrote:

    > 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".

    --
    There's this weird kid watching everything that I type
    sitting beside me. Yes you. I don't see anyone else.
    Go away. Obviously I don't want you to do this.
     
    Auric__, Apr 8, 2014
    #2
    1. Advertisements

  3. Re: Curious string error in named range automation attempt - justdoesn't seem to make sense!

    On Tuesday, April 8, 2014 1:19:48 PM UTC+8, Mark Stephens wrote:
    > 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
     
    Mark Stephens, Apr 8, 2014
    #3
  4. Mark Stephens

    GS Guest

    Re: Curious string error in named range automation attempt - just doesn't seem to make sense!

    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
     
    GS, Apr 8, 2014
    #4
  5. Re: Curious string error in named range automation attempt - justdoesn't seem to make sense!

    On Tuesday, April 8, 2014 1:19:48 PM UTC+8, Mark Stephens wrote:
    > 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.
     
    Mark Stephens, Apr 9, 2014
    #5
  6. Mark Stephens

    GS Guest

    Re: Curious string error in named range automation attempt - just doesn't seem to make sense!

    > 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
     
    GS, Apr 9, 2014
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.