counting rows to an empty cell and making a chart

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I'm stuck with the next problem. I have two rows of data that look
like this:

0 97.7 1
20 95.6 1
40 89.6 1
60 89.0 1

0 90.4 2
20 90.7 2
40 90.9 2
60 89.9 2
80 88.4 2

0 89.8 3
.... .... and this goes on to 160

What I want to make charts with this data. Like on the x-axis the data
in the left columnt and on Y-axis data from the right column. So this
goes pretty easy with this:

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection(1).XValues = "='TEMPLATE'!R6C6:R17C6"
ActiveChart.SeriesCollection(1).Values =
"='TEMPLATE'!R6C15:R17C15"
ActiveChart.SeriesCollection(1).name = "='TEMPLATE'!R6C7"
etc...

Now, is there a way to make this go automatically? So that the macro
finds the first empty cell in the row, makes a chart with the data,
gives the chart a name, and then goes on to the next set of data and
makes a new chart...?
 
Nick -

This isn't exactly what you're going to need, because it just uses the
column A data for the X values, and B for the Y, plus the first cell in
column C in that block of data for the series name. Your code uses
columns 6, 15, and 7 for this. But it steps down to each block, and
uses just that block for the next chart.

Sub MakeCharts()
Dim myCell As Range
Dim myCell2 As Range
Dim myRange As Range
Dim myChartObject As ChartObject
Dim mySeries As Series

Set myCell = ActiveSheet.Cells(1, 1)
Debug.Print myCell.Address
If Len(myCell.Text) = 0 Then
Set myCell = myCell.End(xlDown)
Debug.Print myCell.Address
End If

Do
Set myCell2 = myCell.End(xlDown)
Set myRange = ActiveSheet.Range(myCell, myCell2)
Set myChartObject = ActiveSheet.ChartObjects.Add _
(myCell.Offset(0, 3).Left, myCell.Top, 350, 275)
Set mySeries = myChartObject.Chart.SeriesCollection.NewSeries
With mySeries
.Values = myRange.Offset(0, 1)
.XValues = myRange
.Name = "=" & myRange.Offset(0, 2).Resize(1, 1).Address _
(ReferenceStyle:=xlR1C1, external:=True)
End With
Set myCell = myCell2.End(xlDown)
Debug.Print myCell.Address
Loop Until myCell.Row = 65536
End Sub

- Jon
 
Jon, thank you very much. I guess I should get a bit more into VBA.
Anyways, this works very well, but I want to make some changes.
Because I always have four sets of data I would like to put them all
into one chart. As mentioned before, data look something like this:

0 98.7 1
20 99.4 1

0 98.6 2
20 98.7 2

0 98.2 3
20 99.5 3

0 97.3 4
0 98,5 4

So I changed your code into this:

"Sub MakeCharts()
Dim myCell As Range
Dim myCell2 As Range
Dim myRange As Range
Dim myChartObject As ChartObject
Dim mySeries As Series

Dim counter As Single
counter = 0

Set myCell = ActiveSheet.Cells(1, 1)
Debug.Print myCell.Address
If Len(myCell.Text) = 0 Then
Set myCell = myCell.End(xlDown)
Debug.Print myCell.Address
End If

Do

Set myChartObject = ActiveSheet.ChartObjects.Add _
(myCell.Offset(0, 3).Left, myCell.Top, 350, 275)

Do While counter < 4

Set myCell2 = myCell.End(xlDown)
Set myRange = ActiveSheet.Range(myCell, myCell2)

Set mySeries = myChartObject.Chart.SeriesCollection.NewSeries
With mySeries

.Values = myRange.Offset(0, 1)
.XValues = myRange
.Name = "=" & myRange.Offset(0, 2).Resize(1, 1).Address _
(ReferenceStyle:=xlR1C1, external:=True)
.ChartType = xlXYScatterSmooth

End With


Set myCell = myCell2.End(xlDown)
Debug.Print myCell.Address

counter = counter + 1

Loop


Loop Until myCell.Row = 65536

End Sub "


And this works fine as long as I only make one chart. After that the
program is stuck. Any idea why?
And I also had an other question. How can I give a name to the chart?
Something like:

With ActiveChart
..HasTitle = True
..ChartTitle.Text = "Blabla"
End With

doesn't work.

Thanx in advance.
 
Back
Top