Hi daniel,

You are almost there, just need to define rowbegin, rowend, and
columnofinterest as the row and column numbers - not the contents of the
cells as in your code.

See amended code...

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

' these are the values in cells(x,y)
' rowbegin = Cells(1, 1)
' rowend = Cells(2, 1)
' columnofinterest = Cells(3, 1)

' you need to set row and column number...
rowbegin = 1
rowend = 3
columnofinterest = 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

' also here you need to activate the sheet again, because the chart
' is active at this point and Cells(1,1) won't work
Sheets(1).Activate
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

You might consider the following way of doing the same thing.
This gives you somewhat better control of how the chart is positioned.

Sub BetterWay()
Dim rngSeries As Range
Dim oCht As ChartObject
Dim strTitle As String

Cells(1, 1) = 8
Cells(2, 1) = 20
Cells(3, 1) = 3

strTitle = "My New Chart"

' set the range to chart with a simple statement
Set rngSeries = Worksheets("Sheet1").Range("A1:A3")
' or use End(xlDown) to set a range dynamically
' With Worksheets("Sheet1")
' Set rngSeries = .Range("A1", .Range("A1").End(xlDown))
' End With

' add a chart object to the worksheet - this method gives you
' control over where the chart object is placed and its size
Set oCht = ActiveSheet.ChartObjects.Add(100, 30, 400, 250)

' then use the chart wizard method of the chart to add a series
' note that oCht is the chart object and oCht.Chart is the chart
' contained by that object
oCht.Chart.ChartWizard Source:=rngSeries, gallery:=xlLine, Title:=strTitle

End Sub

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com


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