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

[Ajay_N]

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

Hello,

I am trying to generate a chart with multiple series. The number o
series is not fixed and can be any number. I recorded a macro and the
modified the code. 'Counter' refers to the number of the series and
have referenced it in a worksheet. I am getting the error in the lin
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 Where:=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 = "Normalize
values"
End With


End Su

 

[Jon Peltier]

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/ChartsHowTo/QuickChartVBA.html

- Jon