Automatic Macro to create 200 graphs

Discussion in 'Excel Charting' started by JoeCL, Jun 15, 2013.

  1. JoeCL

    JoeCL Guest

    Hi Everyone,

    I'm struggling with a macro to automatically create about 200 line graphs.

    There are a variable number of datapoints in each graph. The data are all in
    one column separated by blank lines, like:

    123
    456
    789
    887
    762

    1
    2
    3

    55
    44
    88
    447

    I'm new to macros and have tried recording a macro to see the code. The
    macro is fine for creating a single graph, but I don't know how to get it to
    automatically repeat this to create all the graphs, especially when there
    are a variable number of datapoints from one batch to the next of this
    spreadsheet.

    Can anyone suggest a solution?

    I would very much appreciate your help.

    Kindest regards,

    Julian

    :)
     
    JoeCL, Jun 15, 2013
    #1
    1. Advertisements

  2. JoeCL

    Spiggy Topes Guest

    On Friday, June 14, 2013 9:58:04 PM UTC-7, JoeCL wrote:
    > Hi Everyone,
    >
    >
    >
    > I'm struggling with a macro to automatically create about 200 line graphs..
    >
    >
    >
    > There are a variable number of datapoints in each graph. The data are allin
    >
    > one column separated by blank lines, like:
    >
    >
    >
    > 123
    >
    > 456
    >
    > 789
    >
    > 887
    >
    > 762
    >
    >
    >
    > 1
    >
    > 2
    >
    > 3
    >
    >
    >
    > 55
    >
    > 44
    >
    > 88
    >
    > 447
    >
    >
    >
    > I'm new to macros and have tried recording a macro to see the code. The
    >
    > macro is fine for creating a single graph, but I don't know how to get itto
    >
    > automatically repeat this to create all the graphs, especially when there
    >
    > are a variable number of datapoints from one batch to the next of this
    >
    > spreadsheet.
    >
    >
    >
    > Can anyone suggest a solution?
    >
    >
    >
    > I would very much appreciate your help.
    >
    >
    >
    > Kindest regards,
    >
    >
    >
    > Julian
    >
    >
    >
    > :)


    Try something like this. It identifies blank lines and charts all lines since the previous blank line. It stacks generated charts at reasonable intervals so they're not all on top of each other. It's not bullet proof, but should give you enough to expand on.

    Option Explicit

    Sub Chart_Um()

    Dim i As Integer
    Dim iRangeStart As Integer
    Dim iChartNum As Integer

    iChartNum = 0
    iRangeStart = 1

    For i = 1 To ActiveSheet.UsedRange.Rows.Count + 1

    If Cells(i, 1) = "" Then
    If i > iRangeStart Then
    Range(Cells(iRangeStart, 1), Cells(i - 1, 1)).Select
    ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
    .ChartType = xlColumnClustered
    .ChartArea.Top = 20 + (iChartNum * 220)
    .ChartArea.Height = 200
    iChartNum = iChartNum + 1
    End With
    End If
    iRangeStart = i
    End If
    Next i

    End Sub
     
    Spiggy Topes, Dec 13, 2013
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Albert A. Bartlett

    Bar Graphs, Semi-Log Graphs

    Albert A. Bartlett, Jul 31, 2003, in forum: Excel Charting
    Replies:
    1
    Views:
    401
    Tushar Mehta
    Jul 31, 2003
  2. John Clarke
    Replies:
    3
    Views:
    80
    Jon Peltier
    Jun 25, 2004
  3. G_Freeman

    How do I create Trigonometric function graphs?

    G_Freeman, Sep 17, 2004, in forum: Excel Charting
    Replies:
    0
    Views:
    90
    G_Freeman
    Sep 17, 2004
  4. evh

    How do I create dynamic graphs?

    evh, Oct 26, 2004, in forum: Excel Charting
    Replies:
    1
    Views:
    234
    Jon Peltier
    Oct 27, 2004
  5. DanielWalters6

    Automatic Graphs/ Dynamic Graphs

    DanielWalters6, Jan 24, 2006, in forum: Excel Charting
    Replies:
    1
    Views:
    267
    Andy Pope
    Jan 24, 2006
  6. timc

    sorting data and automatic graphs

    timc, Jan 27, 2006, in forum: Excel Charting
    Replies:
    3
    Views:
    78
    Tushar Mehta
    Jan 27, 2006
  7. Deb
    Replies:
    1
    Views:
    236
    Jon Peltier
    Feb 1, 2006
  8. Elise Scoggins

    Plotting Line Graphs to Specific Bar Graphs

    Elise Scoggins, Jul 3, 2012, in forum: Excel Charting
    Replies:
    0
    Views:
    249
    Elise Scoggins
    Jul 3, 2012
Loading...