charts fucntion help needed

[srinivas]

Hi all,

I am Using MS office 2003. I want to create chart using VBA. I have
written the folowing function.

Public Function AddChartSheet(ByVal x As Long, ByVal y As Long, ByVal z
As Long)
Sheet1.Activate
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheet1.Range(Cells(z + 1, 2),
Cells(x - 1, y - 1)), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
End Function

and calling this function in my main program. But I am geeting and
error "Method of cells of object _Global Failed".

Can any one suggest.

Regards,
Srinivas

 

[Andy Pope]

Hi,

You probably need to fully qualify the Cells object, especially if
Charts.add is creating a new chart sheet which does not have cells.

Cheers
Andy

 

[srinivas]

Hi Andy,

I got the answer. Thanks a lot for your help.

Public Function AddChartSheet(ByVal x As Long, ByVal y As Long, ByVal z
As Long)
Dim ws As Worksheet
Set ws = Worksheets(1)
Sheet1.Activate
'temp = Sheet1.Range(Cells(z + 1, 2), Cells(x - 1, y - 1))
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=ws.Range(ws.Cells(z + 1, 2),
ws.Cells(x - 1, y - 1)), PlotBy:= _
xlColumns
'ActiveChart.ChartType = xlXYScatterLines
ActiveChart.Location Where:=xlLocationAsNewSheet
'With ActiveChart
' .HasTitle = False
' .Axes(xlCategory, xlPrimary).HasTitle = False
' .Axes(xlValue, xlPrimary).HasTitle = False
' End With
End Function

Regards,
Srinivas