Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SetSourceData for Chart
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 Whe=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SetSourceData for Chart
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 "Doerte" wrote in message ... 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 Whe=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SetSourceData for Chart
Thanks Chip. This did the trick.
Doerte "Chip Pearson" wrote: 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 "Doerte" wrote in message ... 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 Whe=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 is missing Built-In Custom Chart Types in Chart Wizard | Charts and Charting in Excel | |||
SetSourceData for UserType XYChart with two series | Charts and Charting in Excel | |||
defined names and setsourcedata | Charts and Charting in Excel | |||
charting problem with activechart.setsourcedata | Excel Discussion (Misc queries) | |||
Activechart.SetSourceData Source??? | Excel Programming |