+ Reply to Thread
Results 1 to 32 of 32

Macro to generate charts, not working correctly.

  1. #1
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Macro to generate charts, not working correctly.

    Hi Guys,
    I've probably done something really stupid but when I run this macro it only creates a chart for one of the data sets and produces two blank charts for the other two sets.
    Here is the code:
    Please Login or Register  to view this content.
    What am I doing wrong? The datarange is between A4:A199 & U4:U199 for each array.
    Thanks guys,
    Alex
    Last edited by alocke; 11-18-2011 at 02:32 AM.

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

    Re: Macro to generate charts, not working correctly.

    First pass through the loop and I assume the activesheet is the one with the data on it, so all is well.

    By the end of the loop the active sheet is Sheet3, where the chart has been moved to. Subsequent passes through the loop will use the activesheet, sheet3, for the data source.

    So you need to reference the correct sheet which has the data on it.

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

  3. #3
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Ahhh! I've been pulling my hair out over that problem for ages, thanks andy - works perfectly.

  4. #4
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Andy sorry for asking this on the same post and not starting a new one but the section of code labelled 'chart title = x'. I wasn't sure how to make this correspond to the dataset.
    Basically titles of each data range are on A4,A204,A404 etc. etc. How can I make the title match that specific rule.

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

    Re: Macro to generate charts, not working correctly.

    You can use the offset function to get to the cell

    You may need to alter the number of rows, -3, to offset.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Quote Originally Posted by Andy Pope View Post
    You can use the offset function to get to the cell

    You may need to alter the number of rows, -3, to offset.

    Please Login or Register  to view this content.
    Does that mean I can't put it where it previously was, as it wouldn't work under activechart any more would it. Where should I move it to?

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

    Re: Macro to generate charts, not working correctly.

    no need to move it. Only change is the "x" to using the range reference.

  8. #8
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Quote Originally Posted by Andy Pope View Post
    no need to move it. Only change is the "x" to using the range reference.
    Sorry I'm probably missing something obvious but it causes a syntax error when I try and run it.

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

    Re: Macro to generate charts, not working correctly.

    Post example workbook so we can see the issue.

  10. #10
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Check out this attachment. It contains only 3 data ranges, but the real one has about 400. Ignore all the working out I've done the important cells are the ones highlighted orange.
    If you can't see any of the values let me know - I'm getting them from an external program.
    Attached Files Attached Files

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

    Re: Macro to generate charts, not working correctly.

    extra bracket was causing the syntax error.

    for the first pass through the loop this will reference A5. If it should be A1 then change -3 to -7

    Please Login or Register  to view this content.
    Hard to tell what it should reference as the cells are all #NAME? errors.

  12. #12
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Quote Originally Posted by Andy Pope View Post
    extra bracket was causing the syntax error.

    for the first pass through the loop this will reference A5. If it should be A1 then change -3 to -7

    Please Login or Register  to view this content.
    Hard to tell what it should reference as the cells are all #NAME? errors.
    You can only see the values if you have Bloomberg. But the data range runs from A8:A43, A208:A243 etc. And the titles are A4, A204 etc. etc,.

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

    Re: Macro to generate charts, not working correctly.

    so use -4 as the row offset value.

  14. #14
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Yeah I was just about to say, changed the offset value to -4 and it works.
    Thanks so much for the help andy,
    Alex

  15. #15
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Andy just one last thing, if I was to generate charts just my data was listed down a column. B1/C1/D1 etc etc. would have the title of the data set. And B2:B37, C2:C37, D2:D37 etc etc. would contain the data range. Also the fixed set of data (i.e the y-axis) was in A2:A37.

    I changed this part of my macro:

    Please Login or Register  to view this content.
    What i've done doesn't work however, could you let me know why if possible.
    Thanks.

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

    Re: Macro to generate charts, not working correctly.

    previously the function create a text description of the non contiguous range.

    Now it is using the Range object so when it does work it will return an array of the values in that range. Is that what you really want?

    If you are just after the text description then try this modification.

    Please Login or Register  to view this content.
    called using
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Yep, works perfectly.
    Cheers.

  18. #18
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Sorry Andy just realized I've done another thing wrong:
    Please Login or Register  to view this content.
    I meant for it to colour the bars, not the text in the cells: I've been trying to figure it out - but I'm fairly bad at charting on VBA, what should it actually read?

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

    Re: Macro to generate charts, not working correctly.

    the way you have it set up means that it is not that simple.

    Remember your range reference describes both the category labels and the data values.

    The routine will colour rhe columns based on data value.
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Andy I think this would work if the charts are on the same spreadsheet as the data but they aren't, am I right in thinking this?

    Just because when I run the script after I've generated the charts... it doesn't work. Am I doing something obvious wrong?
    Cheers.

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

    Re: Macro to generate charts, not working correctly.

    Works for me across sheets.

    Data on sheet1 chart on sheet3
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    I had 'Activechart' Instead of 'Activesheet' my bad.
    Sorry how would I redefine the title for this new format? (So titles are in B2,C2,D2 etc etc.)
    Cheers.

  23. #23
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Also Andy,
    Your macro on that document you posted is defined for only one chart, If I wanted this to colour multiple charts how would I?
    Cheers,
    Alex.

  24. #24
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Quote Originally Posted by alocke View Post
    Also Andy,
    Your macro on that document you posted is defined for only one chart, If I wanted this to colour multiple charts how would I?
    Cheers,
    Alex.
    Sorry that was simple, I've managed to solve that problem.

  25. #25
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Sorry to ask again but I can't seem to get the title on the new dataset working.
    currently I have Offset(-1,1) [I think] But it just gives one title for every dataset. (Titles are in B1,C1 etc etc.)
    Cheers.

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

    Re: Macro to generate charts, not working correctly.

    Can you post your current workbook. Replace cells with UDF that will create #NAME error with static values.

  27. #27
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    So heres an example of what I mean. (See attachment)
    -x,y,z are titles.
    -B2:B37, data for the bar graph of the chart
    -B39:B74 data for the line graph of the chart

    How would I change my script so that:
    1) Values B39:B74 are included in the chart as well, as a line graph on the already existant bar graph.
    2) titles x,y and z are made the chart titles.
    3) #N/A values are ignored from the chart.

    Cheers.
    Attached Files Attached Files

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

    Re: Macro to generate charts, not working correctly.

    Try this for the titles.

    Please Login or Register  to view this content.
    #N/A will not be ignored. In a line chart the data marker will be omitted.
    If you hide the rows the data point will be removed. But with multiple data series on the same row this appraoch will not work.
    You would need to re create only valid data for the chart.

  29. #29
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Hey, so it pretty much works except the linegraph corresponds to the same data as the bar graph.
    - If you see on the workbook I posted, the linegraph data is the data under the barchart data (Cells 39 - 74) or should the macro already do this?

    Cheers.

  30. #30
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Also how would you display the second x axis... Mine isn't appearing when I run the script?
    Cheers.

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

    Re: Macro to generate charts, not working correctly.

    First you need to load the 2nd set of data references into an array.
    This use that for the 2nd series before moving it to the secondary axis.

    Please Login or Register  to view this content.

  32. #32
    Registered User
    Join Date
    11-09-2011
    Location
    Central, Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Macro to generate charts, not working correctly.

    Hi andy, sorry I didn't acknowledge this post at the time of you posting it!
    I hope this doesn't go against the forum rules, but I thought it would be easier to relate back to if I didn't post it as a new thread.

    The macro works perfectly btw. But I was wondering if I were to have say, 300+ charts generated is there a bit of code I can include to make all the charts visible. What I mean by this is, upon making the charts they all appear one on top of the other, and instead of moving them manually Id like to get a bit of code to do that. Is there a quick fix to this? I was thinking if each chart was made into an object and moved into a word document it could work - or is there amore simple way to do this.

    Thanks in advance!

+ 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