SetSourceData for Chart

[Guest]

Hi,

I need to add many charts to an Excel Worksheet. The data are based in
columns.
Basically, I need a separate chart for every 10 entries.

I was hoping to be able to set the data source with the follwing code:

ActiveChart.SetSourceData Source:=Sheets("RTK_Comp").Range(Cells(7 + i, 30),
Cells(16 + i, 30)), PlotBy:=xlColumns

I get this run-time error: Method "Cells" of object "_Global" failed.

Has anybody any idea where I have been going wrong?

The full code is:

Sub draw_charts()

Worksheets("RTK_Comp").Activate
counter1 = 0

Do
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("RTK_Comp").Range(Cells(7 +
counter1, 30), Cells(16 + counter1, 30)), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues =
Worksheets("RTK_Comp").Range(Cells(7 + counter1, 25), Cells(16 + counter1,
25))
ActiveChart.Location Where:=xlLocationAsObject, Name:="RTK_Comp"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Test Chart"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "GPS Seconds"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Error [m]"
End With
counter1 = counter1 + 10
Loop Until ActiveSheet.Cells(7 + counter1, 25).Value = ""

End Sub


Thanks a lot,
Doerte

 

[Chip Pearson]

If the worksheet RTK_Comp is not the active sheet when the code
is executed, you need to qualify the Cells property with the
worksheet name. For simplicity, use the With statement:


With Sheets("RTK_Comp")
ActiveChart.SetSourceData Source:=
..Range(.Cells(7+i,30),.Cells(16+i,30), _
PlotBy:=xlColumns
End With

Note the leading periods before Range and Cells.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Thanks Chip. This did the trick.

Doerte

If the worksheet RTK_Comp is not the active sheet when the code
is executed, you need to qualify the Cells property with the
worksheet name. For simplicity, use the With statement:


With Sheets("RTK_Comp")
ActiveChart.SetSourceData Source:=
..Range(.Cells(7+i,30),.Cells(16+i,30), _
PlotBy:=xlColumns
End With

Note the leading periods before Range and Cells.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Hi,

I need to add many charts to an Excel Worksheet. The data are
based in
columns.
Basically, I need a separate chart for every 10 entries.

I was hoping to be able to set the data source with the
follwing code:

ActiveChart.SetSourceData
Source:=Sheets("RTK_Comp").Range(Cells(7 + i, 30),
Cells(16 + i, 30)), PlotBy:=xlColumns

I get this run-time error: Method "Cells" of object "_Global"
failed.

Has anybody any idea where I have been going wrong?

The full code is:

Sub draw_charts()

Worksheets("RTK_Comp").Activate
counter1 = 0

Do
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("RTK_Comp").Range(Cells(7 +
counter1, 30), Cells(16 + counter1, 30)), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues =
Worksheets("RTK_Comp").Range(Cells(7 + counter1, 25), Cells(16
+ counter1,
25))
ActiveChart.Location Where:=xlLocationAsObject,
Name:="RTK_Comp"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Test Chart"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"GPS Seconds"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Error [m]"
End With
counter1 = counter1 + 10
Loop Until ActiveSheet.Cells(7 + counter1, 25).Value = ""

End Sub


Thanks a lot,
Doerte