Macro to Create Hundreds of Graphs

[JoeCL]

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

 

[h2so4]

JoeCL has brought this to us :

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

hi

here is a macro that generates the graphs, but might need some
tailoring.

Sub gengraph()
'size of graph
'height
h = 100
'width
w = 150
'where to place the first graph top
t = 10

'loop through data in column A to deterrmine data series
' assumption data serie delimited by ONE (and only one) blank line
i = 1
fs = 1
While Cells(i, 1) <> ""
While Cells(i, 1) <> ""
i = i + 1
Wend
'fs first line with element of the data serie
' i last line with element of the data serie
Range("A" & fs & ":A" & i - 1).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("A" & fs & ":A" & i - 1)
ActiveChart.ChartType = xlLine
'position chart
With ActiveChart.Parent
.Height = h
.Width = w
.Top = t
.Left = 100
End With
t = t + h
i = i + 1
fs = i
Wend

End Sub

 

[JoeCL]

JoeCL has brought this to us :

hi

here is a macro that generates the graphs, but might need some tailoring.

Sub gengraph()
'size of graph
'height
h = 100
'width
w = 150
'where to place the first graph top
t = 10

'loop through data in column A to deterrmine data series
' assumption data serie delimited by ONE (and only one) blank line
i = 1
fs = 1
While Cells(i, 1) <> ""
While Cells(i, 1) <> ""
i = i + 1
Wend
'fs first line with element of the data serie
' i last line with element of the data serie
Range("A" & fs & ":A" & i - 1).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("A" & fs & ":A" & i - 1)
ActiveChart.ChartType = xlLine
'position chart
With ActiveChart.Parent
.Height = h
.Width = w
.Top = t
.Left = 100
End With
t = t + h
i = i + 1
fs = i
Wend

End Sub

Thank you so much for your kind help with this macro. I am grateful.

Unfortunately, it doesn't quite work in my Excel 2003.

It gives an error on this line: ActiveSheet.Shapes.AddChart.Select

That says: "Object doesn't support this property or method"

Also, I'm not yet sure what your macro does, with respect to making hundreds
of graphs, but it seems the output needs to be each graph on it's own chart
sheet...maybe this is what you have created.

Any thoughts about the error?

Many blessings,

Julian

 

[JoeCL]

Thank you so much for your kind help with this macro. I am grateful.

Unfortunately, it doesn't quite work in my Excel 2003.

It gives an error on this line: ActiveSheet.Shapes.AddChart.Select

That says: "Object doesn't support this property or method"

Also, I'm not yet sure what your macro does, with respect to making
hundreds of graphs, but it seems the output needs to be each graph on it's
own chart sheet...maybe this is what you have created.

Any thoughts about the error?

Many blessings,

Julian

I take that back...I think your macro puts all the graphs on a single sheet.
That may be more convenient after all

All that remains is the solution to the error.

Thank you, kindly,

Julian

 

[h2so4]

I take that back...I think your macro puts all the graphs on a single sheet.
That may be more convenient after all

All that remains is the solution to the error.

Thank you, kindly,

Julian

could you record (in a macro) the generation of one chart, and post it
here ? I don't have excel 2003 and cannot figure out if there is a
specific syntax.

 

[h2so4]

JoeCL expressed precisely :

I take that back...I think your macro puts all the graphs on a single sheet.
That may be more convenient after all

All that remains is the solution to the error.

Thank you, kindly,

Julian

try this one, will generate one sheet per chart

Sub gengraph()

'loop
i = 1
fs = 1
While Worksheets("sheet1").Cells(i, 1) <> ""
While Worksheets("sheet1").Cells(i, 1) <> ""
i = i + 1
Wend
'fs first line with element of the data serie
' i last line with element of the data serie
Charts.Add
With ActiveChart
.ChartType = xlLine
.SetSourceData Source:=Worksheets("sheet1").Range("A" & fs & ":A" & i
- 1)
.Legend.Delete
End With
i = i + 1
fs = i
Wend

End Sub

 

[JoeCL]

IT WORKS!!! See below...

JoeCL expressed precisely :


try this one, will generate one sheet per chart

Sub gengraph()

'loop
i = 1
fs = 1
While Worksheets("sheet1").Cells(i, 1) <> ""
While Worksheets("sheet1").Cells(i, 1) <> ""
i = i + 1
Wend
'fs first line with element of the data serie
' i last line with element of the data serie
Charts.Add
With ActiveChart
.ChartType = xlLine
.SetSourceData Source:=Worksheets("sheet1").Range("A" & fs & ":A" & i -
1)
.Legend.Delete
End With
i = i + 1
fs = i
Wend

End Sub

Dear h2so4,

First, let me say thank you so much for your kind help and time

I was able to look at your code and the code generated by Excel 2003 for a
one graph macro and make it all work. Here is the final result:

Sub MakeHundredsOfGraphs()

'ASSUMES ONE BLANK ROW SEPARATES ONE GRAPH'S DATA FROM THE NEXT
fs = 1 'FIRST LINE OF DATA FOR CURRENT GRAPH
i = 1 'LAST LINE OF DATA FOR CURRENT GRAPH
While Sheets("Sheet1").Cells(fs, 1) <> ""
i = fs + 1
While Sheets("Sheet1").Cells(i, 1) <> ""
i = i + 1
Wend
Charts.Add
With ActiveChart
.Select
.ChartType = xlLine
.SetSourceData Source:=Sheets("Sheet1").Range("A" & fs & " : A" & i - 1),
PlotBy:=xlColumns
.Location Where:=xlLocationAsNewSheet
.Legend.Delete
End With

Sheets("Sheet1").Select

i = i + 1
fs = i
Wend

End Sub

Again, thank you so much for your help. Have a wonderful evening there in
beautiful Belgium.

Many blessings,

Julian

 

[h2so4]

JoeCL expressed precisely :

IT WORKS!!! See below...

Happy that you manage to finalize the solution on your own !

Again, thank you so much for your help. Have a wonderful evening there in
beautiful Belgium.
thanks


Many blessings,

Julian

thanks for your feedback.