Dynamic chart generation: Run time Error '1004: Method 'Cells' of object'_Global' fai
 

--------------------------------------------------------------------------------

Hello,

I am trying to generate a chart with multiple series. The number of
series is not fixed and can be any number. I recorded a macro and then
modified the code. 'Counter' refers to the number of the series and i
have referenced it in a worksheet. I am getting the error in the line
that is bold. Thank you for your help.

The code is as follows:

Dim Counter As Integer
Dim i As Integer

Sheets("With TC").Select
Counter = Cells(38, 13).Value
ActiveChart.ChartType = xlLine
With Sheets("VP_WithTC")
ActiveChart.SetSourceData Source:=.Range(.Cells(2, 13), .Cells(Counter,
16)), PlotBy _
:=xlRows
End With
For i = 2 To Counter
With ActiveChart.SeriesCollection.NewSeries

.VALUES = ACTIVESHEET.RANGE(CELLS(I, 13), CELLS(I, 16))
.XValues = ActiveSheet.Range("E1:H1")
End With

Next i

ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Value Path Graph"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Criteria"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Normalized
values"
End With


End Sub


--
Ajay_N
------------------------------------------------------------------------
Ajay_N's Profile: http://www.excelforum.com/member.php...o&userid=37418
View this thread: http://www.excelforum.com/showthread...hreadid=571032

Dynamic chart generation: Run time Error '1004: Method 'Cells' of object'_Global' fai
 

If your active chart is a chart sheet, then the active sheet is the chart,
not a worksheet. So ActiveSheet.Cells will break.

You could add the chart directly as an embedded chart object, thus keeping
the workbook as the active sheet. Even if the chart is active, you can
reference the cells on the worksheet below.

The syntax is

ActiveSheet.ChartObjects.Add(left, top, width, height)

The dimensions in parentheses are for the embedded chart object, in points.

More VBA charting tips:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Ajay_N" wrote in
message ...

--------------------------------------------------------------------------------

Hello,

I am trying to generate a chart with multiple series. The number of
series is not fixed and can be any number. I recorded a macro and then
modified the code. 'Counter' refers to the number of the series and i
have referenced it in a worksheet. I am getting the error in the line
that is bold. Thank you for your help.

The code is as follows:

Dim Counter As Integer
Dim i As Integer

Sheets("With TC").Select
Counter = Cells(38, 13).Value
ActiveChart.ChartType = xlLine
With Sheets("VP_WithTC")
ActiveChart.SetSourceData Source:=.Range(.Cells(2, 13), .Cells(Counter,
16)), PlotBy _
:=xlRows
End With
For i = 2 To Counter
With ActiveChart.SeriesCollection.NewSeries

VALUES = ACTIVESHEET.RANGE(CELLS(I, 13), CELLS(I, 16))
XValues = ActiveSheet.Range("E1:H1")
End With

Next i

ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
HasTitle = True
ChartTitle.Characters.Text = "Value Path Graph"
Axes(xlCategory, xlPrimary).HasTitle = True
Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Criteria"
Axes(xlValue, xlPrimary).HasTitle = True
Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Normalized
values"
End With


End Sub


--
Ajay_N
------------------------------------------------------------------------
Ajay_N's Profile:
http://www.excelforum.com/member.php...o&userid=37418
View this thread: http://www.excelforum.com/showthread...hreadid=571032