Below is a code chunk that is supposed to add a new chart then add a chart series as the sales numbers for that product are done totalling. It gets past the chart creation in the beginning. However, when I hit the
'Set cNewSeries = cNewChart.SeriesCollection.NewSeries'
line when adding the chart series it gives me a
'Runtime Error 438 Objectdoesn't support this property or method'
What am I doing wrong? I know what that error is supposed to mean, but I see nothing wrong with the way I'm using the object variables. This chart creation code is giving me fits!! Thanks in advance for any help!
'Set cNewSeries = cNewChart.SeriesCollection.NewSeries'
line when adding the chart series it gives me a
'Runtime Error 438 Objectdoesn't support this property or method'
What am I doing wrong? I know what that error is supposed to mean, but I see nothing wrong with the way I'm using the object variables. This chart creation code is giving me fits!! Thanks in advance for any help!
Code:
' adding the chart
Dim cNewSeries As Series
Dim cNewChart As ChartObject
Set cNewChart = ActiveSheet.ChartObjects.Add _
(Left:=0, Width:=350, Top:=0, Height:=350)
cNewChart.Chart.Location Where:=xlLocationAsObject, Name:="Chart"
cNewChart.Chart.ChartType = sChartType
' loop for cycling through desired product codes
nSeriesCounter = 0
nRowCounter = 37
nColumnCounter = 3
For nCodeLoop = 1 To 8
' setting current code value
sCurrentCode = Workbooks("Charts").Names("Code" & nCodeLoop).RefersToRange.Value
' testing to see if there a product code in that position
If (sCurrentCode <> "") Then
' incrementing series counter
nSeriesCounter = nSeriesCounter + 1
' starting loop to gather sales numbers
For nSalesLoop = 0 To (nSeriesDataPoints - 1)
' getting total sales for this iteration
nTotalSales = SUM_SALES_RANGE(sCurrentCode, nSeriesDivisor, (dStartDate + (nSeriesDivisor * nSalesLoop)))
' adding amount to cell on parameters worksheet
Workbooks("Charts").Worksheets("Parameters").Cells((nSeriesCounter + nRowCounter), (nSalesLoop + nColumnCounter)).Value = nTotalSales
Next nSalesLoop
' adding the series
Set cNewSeries = cNewChart.SeriesCollection.NewSeries
With cNewSeries
.Name = sCurrentCode
.Values = ActiveSheet.Range("C38,CY38")
.XValues = ActiveSheet.Range("C39,CY39")
End With
End If
Next nCodeLoop