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>, [email protected] 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 Where:=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
>
>
>