+ Reply to Thread
Results 1 to 15 of 15

Random behaviour for graph-generating macro

  1. #1
    Registered User
    Join Date
    02-28-2015
    Location
    France
    MS-Off Ver
    2010
    Posts
    8

    Random behaviour for graph-generating macro

    Hello,

    My Excel VBA macro has a random behaviour in the generation of stacked histograms.

    Here is my set of data:
    Quarter 1w 2w 3w 4w >4w
    16Q4 100.00% 0.00% 0.00% 0.00% 0.00%
    17Q1 33.33% 0.00% 0.00% 0.00% 66.67%
    17Q2 10.87% 6.52% 13.04% 2.17% 67.39%
    17Q3 10.00% 16.67% 13.33% 6.67% 53.33%
    17Q4 28.13% 6.25% 15.63% 3.13% 46.88%
    18Q1 17.86% 0 14.29% 7.14% 60.71%

    The goal is to have for each quarter, a histogram showing how many times we have 1w, 2w, 3w, 4w, >4w.

    The result is 9 times out of 10 an ugly graph where x and y do not correspond to what I gave, and a number of series greater than 5.

    Here is my code:
    Please Login or Register  to view this content.
    Can you please help as this is getting extremely frustrating?

    Thx in advance!
    Regards,
    Dassadar

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Random behaviour for graph-generating macro

    Hi,

    Please add code tags to your code, as stated in the forum rules, then we can get on with fixing your problem.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    02-28-2015
    Location
    France
    MS-Off Ver
    2010
    Posts
    8

    Re: Random behaviour for graph-generating macro

    Quote Originally Posted by xlnitwit View Post
    Hi,

    Please add code tags to your code, as stated in the forum rules, then we can get on with fixing your problem.
    (just done after seeing the ugly result!)

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Random behaviour for graph-generating macro

    Thank you! Perhaps this adjustment to clear any data that Excel automatically uses for the chart based on the current selection when you create the chart
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-28-2015
    Location
    France
    MS-Off Ver
    2010
    Posts
    8

    Re: Random behaviour for graph-generating macro

    Thx.
    I am getting an error on the .XValues line: method not recognized... probably due to the 2 upper With?

  6. #6
    Registered User
    Join Date
    02-28-2015
    Location
    France
    MS-Off Ver
    2010
    Posts
    8

    Re: Random behaviour for graph-generating macro

    For the clear actually I was using this:
    Please Login or Register  to view this content.

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Random behaviour for graph-generating macro

    Oops. Perhaps this one
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    02-28-2015
    Location
    France
    MS-Off Ver
    2010
    Posts
    8

    Re: Random behaviour for graph-generating macro

    The cleaning of series was the right thing to do : thanks a lot!

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Random behaviour for graph-generating macro

    You're welcome.

  10. #10
    Registered User
    Join Date
    02-28-2015
    Location
    France
    MS-Off Ver
    2010
    Posts
    8

    Re: Random behaviour for graph-generating macro

    By the way I had some other trouble with my same graphs which was how to assign colours to series.
    I tried this:
    Please Login or Register  to view this content.
    But it doesn't seem to have any effect... any idea please?

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,854

    Re: Random behaviour for graph-generating macro

    My first quick answer to this last part is that you are not addressing the series separately.

    You will need to do that for each series in ActiveChart
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  12. #12
    Registered User
    Join Date
    02-28-2015
    Location
    France
    MS-Off Ver
    2010
    Posts
    8

    Re: Random behaviour for graph-generating macro

    Yes, I was just testing to display series 1 in black before setting the right colour to each - but this has no effect on series 1.
    Should I use this instead maybe?
    Please Login or Register  to view this content.

  13. #13
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Random behaviour for graph-generating macro

    This line actually changes the cht variable
    Please Login or Register  to view this content.
    so you need to reassign it before you can alter the chart, like this
    Please Login or Register  to view this content.
    Or you could simply add it to the graphs sheet in the first place.
    Last edited by xlnitwit; 04-23-2018 at 07:09 AM.

  14. #14
    Registered User
    Join Date
    02-28-2015
    Location
    France
    MS-Off Ver
    2010
    Posts
    8

    Re: Random behaviour for graph-generating macro

    It works, thank you so much!

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Random behaviour for graph-generating macro

    You're welcome.

    Please remember to mark the thread Solved using the Thread Tools dropdown at the top of the page.

+ 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. Generating a random name
    By shaibn in forum Excel General
    Replies: 3
    Last Post: 02-09-2016, 10:55 AM
  2. Need help generating bar graph from Excel data via macro
    By tuneloon in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-17-2015, 11:55 AM
  3. Generating a list of Random numbers of random length...
    By Growl in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-19-2013, 12:10 PM
  4. Macro for generating random samples of data and calculating stuff
    By n_ant in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-21-2013, 04:03 AM
  5. Random number generating
    By dagindi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-14-2010, 03:39 PM
  6. Generating a Random Number of Random Numbers
    By Garrus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2010, 09:39 AM
  7. generating a whole random # from a list
    By Arturo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2005, 07:05 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