why does't this work? (chart in excel)

[dreamer]

I have columns with data that look something like this:

A B C
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
20 98.5 4

and then again

0 89.9 1
20 90.3 1
etc...

And what I want to do is to make charts with this data with data fro
column A on the x-Axis and column B on the Y-axis. Column C is used fo
the name of the range.
Because there are always 4 sets of data (column C 1 to 4) I would lik
to put those four into one chart and then go on and make an other char
etc... So I have this code, but the problem is it get's stuck afte
the first chart has been made. Can anyone say way?

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).Addres
_(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 my other question is how can I give a name to those charts?
Spmething like:

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

doesn't work

 

[Jon Peltier]

1. I'd switch it around a bit, find out what the value in C is, and if
it's 1, I'd add a new chart. After I adjusted your adjustments of my
code, I noticed you'd removed the dot in front of Values, XValues, and
Name between With mySeries/End With when assigning these parameters to
the new series.

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)

If myCell.Offset(0, 2).Value = 1 Then
'' make a new chart
Set myChartObject = ActiveSheet.ChartObjects.Add _
(myCell.Offset(0, 3).Left, myCell.Top, 350, 275)
End If

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
Loop Until myCell.Row = 65536

End Sub

2. You need to change the name of the chart object which contains the
chart.

With ActiveChart
.Parent.Name = "Yada Yada"
End With

- Jon