+ Reply to Thread
Results 1 to 7 of 7

Chart Source data range not working properly in VBA

  1. #1
    Forum Contributor
    Join Date
    12-25-2011
    Location
    Lahore, Punjab, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    287

    Chart Source data range not working properly in VBA

    I use following code to establish source data for chart:

    Please Login or Register  to view this content.
    For .Range("C5:D7") this chart is working fine with VBA.

    But if I change the range then this chart does not work fine. For example if I change range to ("C5:N14") in vba then the produced chart looks very strange and does not contain values from the range ("C5:N14") any ways.
    Attached Files Attached Files
    Last edited by caabdul; 01-25-2014 at 08:38 AM.

  2. #2
    Forum Contributor
    Join Date
    12-25-2011
    Location
    Lahore, Punjab, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    287

    Re: Chart Source data range not working properly in VBA

    Please somebody suggest me some alternative way in above situation.
    Isn't there any other way through which I can use the chart source range ("C5:N14") in VBA?

  3. #3
    Forum Contributor
    Join Date
    12-25-2011
    Location
    Lahore, Punjab, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    287

    Re: Chart Source data range not working properly in VBA

    I have found two similar posts at following links. It seems there has not been any solution to this problem. I struck on this!! Please help somebody

    http://www.mrexcel.com/forum/excel-q...ge-itself.html
    http://www.excelforum.com/excel-prog...by-itself.html

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

    Re: Chart Source data range not working properly in VBA

    Change your formula to

    =IFERROR(VLOOKUP($B5,INDIRECT("'" & C$4 & "'!B:C"),2,FALSE),NA())

    That way excel will not assume the data is text to be used as category labels.

    Alternatively you could change your code to add each series
    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  5. #5
    Forum Contributor
    Join Date
    12-25-2011
    Location
    Lahore, Punjab, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    287

    Re: Chart Source data range not working properly in VBA

    Thank you Andy, You assisted when I had almost lost my hopes.

    You have given two alternatives, the first one was fine but it would have not fit in my other requirements. Your second alternative really ROCKS!!!

    I am new to VBA but learning quickly. I have wrote above code myself, thank you for your assistance in cleaning many unwanted text from code. I have learnt a lot from your code.

    Although the following string from your code was responsible to solve my core problem but I coould not understand the code grammer syntax etc.

    Please Login or Register  to view this content.
    I would appreciate some more of your effort if you can kindly explain (Just a simple translation into english) what is above code about.

    For example:
    Code = iferror(C1/F4,0)
    English = If C1/F4 results in some error, then write Zero.

    Thank you again

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

    Re: Chart Source data range not working properly in VBA

    rngRow references a single row of data used for a series, for example C5:C14
    The .cells(1,1).Offset(0,-1) is the location of the cell containing text to use for the series name. First cell in C5:C14 offset set zero rows and -1 column. So this points to B5.
    In order to have the Name property of the series linked to B5, rather than hold static text, we use assign the cell reference. The True,True,xlR1C1 arguments tell it to provide a absolute reference in row/column notation.

    rngRow.Parent.Name provides the name of the worksheet. The rest of the code simple places single quotes around the sheet name to handle spaces if present. The ! is normal syntax to separate sheet name from cell reference. The result would be

    ='Yearly'!R5C2

  7. #7
    Forum Contributor
    Join Date
    12-25-2011
    Location
    Lahore, Punjab, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    287

    Re: Chart Source data range not working properly in VBA

    Excellent Explanation!!! Thank you again

+ 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. [SOLVED] Chart is not working properly
    By K m in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-29-2013, 01:16 PM
  2. Dynamic Range for Chart Source data
    By vlad23 in forum Excel General
    Replies: 25
    Last Post: 06-26-2012, 04:06 PM
  3. pie chart source data range
    By allrryan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2010, 11:53 AM
  4. [SOLVED] Chart: Use range name for source data
    By sk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-06-2005, 04:05 PM
  5. [SOLVED] Chart source data range
    By Don Rouse in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2005, 03:06 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