Problem with chart creation

[daniel chen]

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

 

[Jim Cone]

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


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 chen]

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