G
Guest
Simply recording the macro gave me the following:
******************************
Sub nHourCharting()
'
' nHourCharting Macro
' Macro recorded 10/12/2004 by Matt
'
'
Range("A5:C29").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Last_n_Hours_Graph").Range("A5:C29" _
), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "=Last_n_Hours_Graph!R6C1:R29C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Last_n_Hours_Graph"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "HOEP 5 minute Pricing"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Hour"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price"
End With
End Sub
*************************
This works fine.
There is more data on the sheet, and I want the user be able to input the
range as a variable based on the hours the user wants to show.
Without making the range variables (just for testing), but just using the
cell syntax for the ranges, I tried the following:
*****************************************
Sub nHourChartingTesting()
Range(Cells(5, 1), Cells(29, 3)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Last_n_Hours_Graph").Range(Cells(5, 1), Cells(29, 3)),
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = Range(Cells(6, 1), Cells(29,
3)) '"=Last_n_Hours_Graph!R6C1:R29C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Last_n_Hours_Graph"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "HOEP 5 minute Pricing"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Hour"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price"
End With
End Sub
***************
It seems to accept the first use of the range property, but not the 2nd in
the following line:
ActiveChart.SetSourceData
Source:=Sheets("Last_n_Hours_Graph").Range(Cells(5, 1), Cells(29, 3)),
PlotBy:=xlColumns
It gives me the error:
Method "cells" of object '_Global' failed
Any ideas?
Thanks,
Matt Lawson
******************************
Sub nHourCharting()
'
' nHourCharting Macro
' Macro recorded 10/12/2004 by Matt
'
'
Range("A5:C29").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Last_n_Hours_Graph").Range("A5:C29" _
), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "=Last_n_Hours_Graph!R6C1:R29C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Last_n_Hours_Graph"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "HOEP 5 minute Pricing"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Hour"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price"
End With
End Sub
*************************
This works fine.
There is more data on the sheet, and I want the user be able to input the
range as a variable based on the hours the user wants to show.
Without making the range variables (just for testing), but just using the
cell syntax for the ranges, I tried the following:
*****************************************
Sub nHourChartingTesting()
Range(Cells(5, 1), Cells(29, 3)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Last_n_Hours_Graph").Range(Cells(5, 1), Cells(29, 3)),
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = Range(Cells(6, 1), Cells(29,
3)) '"=Last_n_Hours_Graph!R6C1:R29C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Last_n_Hours_Graph"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "HOEP 5 minute Pricing"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Hour"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price"
End With
End Sub
***************
It seems to accept the first use of the range property, but not the 2nd in
the following line:
ActiveChart.SetSourceData
Source:=Sheets("Last_n_Hours_Graph").Range(Cells(5, 1), Cells(29, 3)),
PlotBy:=xlColumns
It gives me the error:
Method "cells" of object '_Global' failed
Any ideas?
Thanks,
Matt Lawson