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 Whe=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

Srinivas,

I haven't tested it but I would guess that you need to qualify the cells
reference, since you have added a chart sheet (which does not have cells).

Try this
ActiveChart.SetSourceData Source:=Sheet1.Range(Sheet1.Cells(z + 1,
2),Sheet1.Cells(x - 1, y - 1)), etc...

Robin Hammond
www.enhanceddatasystems.com


"srinivas" wrote in message
oups.com...
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 Whe=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

Srinivas,
When you add the chart, it becomes the active sheet.
"Cells" refers to the active sheet.
Add "Sheet1." in front of "Cells" (two places).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"srinivas"

wrote in message
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 Whe=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