You can actually do this without VBA. See examples 3 and 4 in
Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <uk0wf.235255$qk4.212223@bgtnsc05-
news.ops.worldnet.att.net, says...
Sheet1 was populated with column arrays in Range("A8:J50").
I wanted to creat a chart for column array as specified in cells A1, A2, A3
as rowbegin, rowend,and columnofinterest respectively.
Soon after the chart was created all the values in Cells(*, *) were lost,
and the macro ceased to function properly.
Can you help me? Thanks

Sub Creat_a_chart()
Dim rowbegin, rowend, columnofinterest As Double
Cells(1, 1) = 8 ' as given
Cells(2, 1) = 20 ' as given
Cells(3, 1) = 3 ' as given
rowbegin = Cells(1, 1)
rowend = Cells(2, 1)
columnofinterest = Cells(3, 1)
Range(Cells(rowbegin, columnofinterest), Cells(rowend,
columnofinterest)).Select
Charts.Add
' From this point on, all the Cells(*, *) failed _ stating "Method
'Cells' of object '_Global' failed "

ActiveChart.ChartType = xlLineMarkers
On Error Resume Next
ActiveChart.SetSourceData Source:= _
Sheets("Sheet1").Range(Cells(rowbegin, columnofinterest),
Cells(rowend, columnofinterest)), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1"
ActiveChart.HasLegend = False
Cells(1, 2) = Cells(1, 1).value ' value lost
Cells(2, 2) = Cells(2, 1).value ' value lost
Cells(3, 2) = Cells(3, 1).value ' value lost
End Sub