Macro to create multiple charts?

[Christina]

Hi,
I have a bunch of excel files full of data, and each one contains
information which needs to be made into 30 charts (scatterplots). I have
been hand selecting the data and then creating each chart manually, but I am
hoping there is a way to automatize the process. Basically what I need to do
is this:

3 adjacent columns of data
look down column 1 for the word "stop" (the rest are all numbers)
when you get to stop, select all three columns until the next time there's a
"stop" in column 1
make the scatterplot
do it again from the next stop (the stop lines can just be skipped when
selecting data for the chart)

Does anyone have any pointers for me?

Thanks so much!
Christina

 

[Jon Peltier]

Here's something quick and dirty. It uses the first column as X values and
the second and third as Y values for two series. Activate a worksheet with
the data separated by cells that say "stop", and it will knock out the
charts.

- Jon

 

[Jon Peltier]

Forgot the code!

Sub ChartBetweenStops()
Dim rStop1 As Range
Dim rStop2 As Range
Dim rStopFirst As Range
Dim rChartData As Range
Dim cht As Chart

ActiveSheet.Range("A1").Select
Set rStopFirst = ActiveSheet.Cells.Find(What:="stop", After:=ActiveCell)
Set rStop2 = rStopFirst
Do
Set rStop1 = rStop2
Set rStop2 = Cells.FindNext(After:=rStop1)
If rStop2.Address = rStopFirst.Address Then Exit Do
Set rChartData = rStop1.Offset(1).Resize(rStop2.Row - rStop1.Row - 1, 3)
Set cht = ActiveSheet.ChartObjects.Add(rStop1.Offset(, 3).Left,
rStop1.Offset(1).Top, 250, 200).Chart
With cht
.ChartType = xlXYScatter
.SetSourceData Source:=rChartData
' include other chart formatting here
End With
Loop
End Sub


- Jon