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

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike Fogleman

    Just Curious

    Mike Fogleman, Sep 30, 2004, in forum: Excel Programming
    Replies:
    7
    Views:
    118
    Mike Fogleman
    Sep 30, 2004
  2. JJ
    Replies:
    3
    Views:
    209
  3. Steve
    Replies:
    1
    Views:
    81
    Tom Ogilvy
    Nov 8, 2005
  4. Myles

    Speed of REPLACE-just curious

    Myles, Aug 16, 2006, in forum: Excel Programming
    Replies:
    2
    Views:
    72
    Myles
    Aug 17, 2006
  5. robs3131

    Run-Time error 1004 issue just not making sense

    robs3131, Jun 5, 2007, in forum: Excel Programming
    Replies:
    3
    Views:
    205
    robs3131
    Jun 5, 2007
  6. John

    just curious Xlpart xlWhole

    John, Jul 26, 2009, in forum: Excel Programming
    Replies:
    2
    Views:
    112
  7. Date conversion doesn't make sense

    , Nov 26, 2009, in forum: Excel Programming
    Replies:
    3
    Views:
    179
    Peter T
    Nov 26, 2009
  8. Luc
    Replies:
    6
    Views:
    281
    michdenis
    Jan 13, 2010
Loading...