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

Ask a Question

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.