Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Macro for charts with dif ranges (2000)

    Hi,

    My daughter is working in a lab and collecting 24-hr data on various observations. She has the data in a worksheet going down cols. She wants to chart each 24-hr set as a separate chart.

    So for example, cols A-K are filled with data for, say, about 240 rows. She wants a chart for A1:A24, another chart for A25:A48,...,another chart for B1:B24, etc. One set of 24, one chart; no multiple series, nothing fancy. Of course, it should be automated via a macro so that all she has to do is indicate the starting point of the data set, press the magic (that is, macro) button (on the toolbar of course) and she gets a chart.

    I was able to automate a part of this by defining a range called data_set starting at A1. But the Refers To was actually an offset from A1 depending on the row/col of the first cell of the current group of 24. So the range data_set actually changed each time. I then ran the chart wizard. On step 2, I changed the data series to data_set and got what I wanted. With each change for the start of the data_set range, I would get a new chart. Great.

    Now I tried to automate this by recording the above step in a macro. When I got to step 2 of the chart wizard, I changed the current cell selection of 24 to data_set. When I got to the end, I stopped the macro and looked at the VBA. Lo and behold, the macro recorder substituted the current absolute cell range for data_set.

    Clearly, not of much use for the other data sets.

    I tried various substitutions directly in the VBA where the data series was specified but all to no avail.

    Suggestions? TIA.

    Fred

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for charts with dif ranges (2000)

    This ref may help

    <A target="_blank" HREF=http://www.beyondtechnology.com/geeks021.shtml>Charting Dynamic Ranges</A>

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Macro for charts with dif ranges (2000)

    Mike,

    Thks for the pointer. I think I can see my way thru this to what my daughter needs. The catch seems to be editing the SERIES. I didn't notice it when I recorded the macro. I did both of the "incorrect" things noted in the reference. Let's see if she's up to it. I think her problem is a little different in that she is grabbing a different group of 24 consecutive rows for each graph and the cols change after about 10 groups of 24 per col. I think it will take a little VBA beyond just what the macro recorder will record. Let's see what she comes up with, with or w/o my help.

    Fred

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro for charts with dif ranges (2000)

    If you post your code, we can fix it! (Tomorrow, for me) --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Macro for charts with dif ranges (2000)

    Sammy,

    Thks for the offer. Let's see where my daughter gets to. She's 20, going into her junior yr in college, and very bright. She can take a crack at it first. But we'll let you know.

    thankful dad

Posting Permissions

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