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? 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

Problem with chart creation.
 

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

Problem with chart creation.
 

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" wrote in message
...
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

Problem with chart creation.
 

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, 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 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