Adding a Series to a Chart

[dcanham]

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!

    ' 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

 

[NdNoviceHlp]

Something like this should work. Dave

 Dim Xvalues As Range, Yvalues As Range, ChartRange As Range
 Xvalues = ActiveSheet.Range("C39,CY39")
 Yvalues = ActiveSheet.Range("C38,CY38")
 Set ChartRange = ActiveSheet.Range(Xvalues, Yvalues)

 With cNewChart.Chart
 .SeriesCollection.Add Source:=ChartRange
 .Name = sCurrentCode
 End With

edit: whoops that added a series to an existing chart. Try this...

Dim Xvalues As Range, Yvalues As Range, ChartRange As Range
 Xvalues = ActiveSheet.Range("C39,CY39")
 Yvalues = ActiveSheet.Range("C38,CY38")
 Set ChartRange = ActiveSheet.Range(Xvalues, Yvalues)

With cNewChart.Chart
.SetSourceData Source:=ChartRange
.Name = sCurrentCode
End With

 

[dcanham]

Hmmmm, ok I'll give this a try when I get back to work. Why would the way I was doing it throw an error though? I think I'm missing some major theory on how charts work. In prior code, instead of the object variable, I used 'ActiveChart.'. It had issues with that as well. I thought it was due to the fact that I had gone to another workbook in code before that and it was no longer 'Active'. Your code seems to be simply a variant on the way I was doing it to begin with?