Problem with chart creation

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?

Sub Creat_a_chart()
Dim rowbegin, rowend, columnofinterest As Double

Cells(1, 1) = 8 ' as given example
Cells(2, 1) = 20 ' as given example
Cells(3, 1) = 3 ' as given example
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 lost
Cells(2, 2) = Cells(2, 1) ' value lost
Cells(3, 2) = Cells(3, 1) ' value lost
End Sub

daniel,

"Charts.Add" creates a new chart sheet, which becomes the active sheet.
"Cells", with no sheet qualifier, refers to the active sheet.
There are no cells on a chart sheet.

'-----------------
Sub Create_a_chart()
Dim rowBegin as Long
Dim rowEnd as Long
Dim columnOfInterest As Long
Dim objSheet as Excel.Worksheet

Set objSheet = ActiveSheet
objSheet.Cells(1, 1).Value = 8
'etc
'--------------------------

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"daniel chen" <[email protected]> wrote in message news:[email protected]...
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?

Sub Creat_a_chart()
Dim rowbegin, rowend, columnofinterest As Double

Cells(1, 1) = 8 ' as given example
Cells(2, 1) = 20 ' as given example
Cells(3, 1) = 3 ' as given example
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 lost
Cells(2, 2) = Cells(2, 1) ' value lost
Cells(3, 2) = Cells(3, 1) ' value lost
End Sub

Hi Jim,
Thanks for the explanation. I have it working.

"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
> daniel,
>
> "Charts.Add" creates a new chart sheet, which becomes the active sheet.
> "Cells", with no sheet qualifier, refers to the active sheet.
> There are no cells on a chart sheet.
>
> '-----------------
> Sub Create_a_chart()
> Dim rowBegin as Long
> Dim rowEnd as Long
> Dim columnOfInterest As Long
> Dim objSheet as Excel.Worksheet
>
> Set objSheet = ActiveSheet
> objSheet.Cells(1, 1).Value = 8
> 'etc
> '--------------------------
>
> Regards,
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> "daniel chen" <[email protected]> wrote in message
> news:[email protected]...
> 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?
>
> Sub Creat_a_chart()
> Dim rowbegin, rowend, columnofinterest As Double
>
> Cells(1, 1) = 8 ' as given example
> Cells(2, 1) = 20 ' as given example
> Cells(3, 1) = 3 ' as given example
> 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 lost
> Cells(2, 2) = Cells(2, 1) ' value lost
> Cells(3, 2) = Cells(3, 1) ' value lost
> End Sub
>
>