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?

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.
Similar Threads
  1. Mike Fogleman

    Just Curious

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

    Speed of REPLACE-just curious

    Myles, Aug 16, 2006, in forum: Excel Programming
    Replies:
    2
    Views:
    78
    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:
    211
    robs3131
    Jun 5, 2007
  6. John

    just curious Xlpart xlWhole

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

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