Automatic Macro to create 200 graphs
 

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

:-)

Is there anything that separates the data so you know one data set from the next, if so you can write some code to look for the data separator and just use the code to generate a graph and repeat every time it hits that separator

Automatic Macro to create 200 graphs
 

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 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



:-)

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