Hi Ed,
First I thank you sincerely.
I got your idea and my final product is_

Sub FinalWay()
Dim rngSeries As Range
Dim oCht As ChartObject
Dim strTitle As String
Dim rowbegin, rowend, columnofinterest As Long
rowbegin = InputBox(prompt:=" Enter the beginning row")
rowend = InputBox(prompt:=" Enter the ending row")
columnofinterest = InputBox(prompt:=" Ender the column of interest")
Cells(1, 1) = rowbegin
Cells(2, 1) = rowend
Cells(3, 1) = columnofinterest
strTitle = "My New Chart"
Set rngSeries = Worksheets("Sheet1").Range(Cells(rowbegin, _
columnofinterest), Cells(rowend, columnofinterest))
Set oCht = ActiveSheet.ChartObjects.Add(100, 30, 400, 250)
oCht.Chart.ChartWizard Source:=rngSeries, gallery:=xlLine, Title:=strTitle
oCht.Chart.HasLegend = False
End Sub


"Ed Ferrero" <[email protected]> wrote in message
news:%[email protected]...
> 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 Where:=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 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
>>
>
>