Macro to generate new graph with variable range

[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

 

[Guest]

Not sure if this will help, but will work if the area you want is isolated.
Selection.CurrentRegion.Select