+ Reply to Thread
Results 1 to 7 of 7

Charts and VBA

  1. #1
    Registered User
    Join Date
    01-16-2020
    Location
    Norwalk CT
    MS-Off Ver
    CT
    Posts
    4

    Charts and VBA

    I am trying to repurpose an existing series that I have in multiple charts-there are well over a hundred charts in one worksheet, so trying to do via vba. Warning: Very new to VBA!

    In each chart, Series 1 references data in columns A-C, but different rows. So for example Series 1 in chart 1 is A1:C1, and Series 1 in Chart 2 is A2:C2. Is there a way using vba to change Series 1 in all of my charts to refer to columns D:F, without changing the reference to the rows? (so end result would be Series 1 in chart 1 D1:F1, and Chart 2 D2:F2)?
    Thank you in advance.

  2. #2
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,669

    Re: Charts and VBA

    hi
    try this
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-16-2020
    Location
    Norwalk CT
    MS-Off Ver
    CT
    Posts
    4

    Re: Charts and VBA

    Hi Nicky, thank you so much for your response! Its not 100% working for me but I need to play around a bit as I may not have asked my question in its entirety. If I can just ask, the (2) above in the code, what does that refer to/mean?

  4. #4
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,669

    Re: Charts and VBA

    Hi

    the split command breaks up the chart's formula into an array, separated by commas, so a formula like this

    =SERIES(,Data1!$A$11:$A$29,Data1!$B$11:$B$29,1)

    becomes an array with 4 elements

    =SERIES(
    Data1!$A$11:$A$29
    Data1!$B$11:$B$29
    1)

    the (2) returns the third element of that array (by default these start at 0):

    Data1!$B$11:$B$29

    which is the part of the formula with the address of the source data for the data series

    the Replace code replaces the old columns with the new ones in this address

    the final "txt = " creates a formula with the new address replacing the old

    hope that helps
    Last edited by NickyC; Yesterday at 01:24 AM. Reason: clarify

  5. #5
    Registered User
    Join Date
    01-16-2020
    Location
    Norwalk CT
    MS-Off Ver
    CT
    Posts
    4

    Re: Charts and VBA

    Nicky thank you! I so appreciate you taking your time to explain that to me, really does help me out a lot. If I can ask one more question (sorry!). I am assuming that the (1) after SeriesCollection refers to Series 1. So if I have 2 series in my chart, only series 1 will be impacted by this macro. Am I correct?

  6. #6
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,669

    Re: Charts and VBA

    yes that's right. In your original post you indicated that series 1 was the series you want to change. If you want to change other series you could repeat specifying other particular series or, if you want to change all series, you could use a for each ... next command like this:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-16-2020
    Location
    Norwalk CT
    MS-Off Ver
    CT
    Posts
    4

    Re: Charts and VBA

    Thank you from the bottom of my heart Nicky! You have been so helpful!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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