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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
dreamer wrote:
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 from
column A on the x-Axis and column B on the Y-axis. Column C is used for
the name of the range.
Because there are always 4 sets of data (column C 1 to 4) I would like
to put those four into one chart and then go on and make an other chart
etc... So I have this code, but the problem is it get's stuck after
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).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 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.