+ Reply to Thread
Results 1 to 3 of 3

Excel : VBA : Chart - Problem setting SeriesCollection.Formula with Named Ranges

  1. #1
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Excel : VBA : Chart - Problem setting SeriesCollection.Formula with Named Ranges

    I am trying to copy a template with a chart which has sheet scoped Named Ranges.

    Problem 1: When the (template) sheet is copied, the Named Ranges are created/present for the new sheet ... but the series ranges for the chart are hard coded. That is, they do not use the sheet scoped Named Ranges in the copy of the chart on the new sheet. Tried and failed to work my way around that.

    So ...

    Problem 2: I thought I'd just set up the SeriesCollection formulae based on the Template worksheet/chart. But I seem to be struggling with that too

    I've checked the basics and the sheet, chart, etc., are all as expected. And, if I use the hard coded values, it happily accepts it. However, when I try to set the SeriesCollection formula with Named Ranges, I get an error.

    This fails:
    Please Login or Register  to view this content.
    Whereas, this works:
    Please Login or Register  to view this content.
    I'm using this to test the theory:
    Please Login or Register  to view this content.
    nr_LR:
    Please Login or Register  to view this content.
    nr_Col_E:
    Please Login or Register  to view this content.
    _20_Day_MA:
    Please Login or Register  to view this content.
    Am I missing something? Well, I guess I must be, but what?

    Any thoughts, advice, comments, alternative approaches welcome.

    Thanks

    TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Excel : VBA : Chart - Problem setting SeriesCollection.Formula with Named Ranges

    Change the name of your named range for values. The leading underscore is causing the problem.

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: Excel : VBA : Chart - Problem setting SeriesCollection.Formula with Named Ranges

    OGG. Thanks Andy.

    Would have replied sooner but testing didn't go as smoothly as I had hoped. For some reason, simply changing the name as you suggested didn't work. Nor did changing it to Twenty_Day_MA. Seems I must have had other issues . Whatever, all the other similar constructs worked and I ended up copying one of them and editing it. FINALLY, it works as expected and I can move on.

    Thanks again

    Trevor

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA - Named ranges in an excel chart
    By revenge4ash8 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2014, 04:39 AM
  2. [SOLVED] Setting up dynamic named ranges in code
    By j_Southern in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-04-2014, 01:03 PM
  3. Excel 2010 Dynamic Chart with named Ranges
    By PaulJGallagher in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-03-2013, 09:06 AM
  4. [SOLVED] Chart+named ranges: undesired points show up at y=0 - problem
    By Alexander_Golinsky in forum Excel General
    Replies: 2
    Last Post: 07-09-2012, 08:29 AM
  5. Dynamic Charts - help setting up with named ranges
    By Alex Aherne in forum Excel General
    Replies: 5
    Last Post: 06-10-2012, 07:17 AM
  6. Chart - Problem getting value from SeriesCollection
    By JohnM3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-18-2011, 01:21 AM
  7. [SOLVED] Named ranges vs setting range in code
    By Tim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2006, 10:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1