Chart Help Please..

[CTKC]

Hi,


Below is Part of my code. I am hoping someone can explain as to why the seriescollection.values do not work properly.An error does not occur but nothing is put into the source data. The series name code changes correctly..

Any help can be appreciated.

Thanks!

Current_Month = Data_Sheet.Cells.Find(What:=FrmChart.CMB_Date.Value, after:=ActiveCell).Column
Start_Month = Data_Sheet.Cells.Find(What:=FrmChart.CMB_Date.Value, after:=ActiveCell).Column - 11
            
Product_Count = 0
Do While Product_Count <= Total_IPT
    RowCount = 7
        Do While Cells(RowCount, 2).Value <> ""
            If Left(Cells(RowCount, 2).Value, Application.WorksheetFunction.Find(" ", Cells(RowCount, 2).Value, 1) - 1) = Product_Team(Product_Count) Then

                Set IPT_Chart = Charts.Add

                Set Chart_Sheet = ActiveSheet
                Chart_Sheet.Name = Product_Team(Product_Count)
                

                
                With IPT_Chart
                    .ChartType = xlLine
                    .SeriesCollection.NewSeries
                    .SeriesCollection(1).Name = Data_Sheet.Cells(RowCount + 10, 4).Value
                    .SeriesCollection(1).Values = Data_Sheet.Range(Cells(RowCount + 10, Start_Month), Cells(RowCount + 10, Current_Month))
                    .SeriesCollection.NewSeries
                    .SeriesCollection(2).Name = Data_Sheet.Cells(RowCount + 11, 4).Value
                    .SeriesCollection(2).Values = Data_Sheet.Range(Cells(RowCount + 11, Start_Month), Cells(RowCount + 11, Current_Month))
                    .SeriesCollection.NewSeries
                    .SeriesCollection(3).Name = Data_Sheet.Cells(RowCount + 12, 4).Value
                    .SeriesCollection(3).Values = Data_Sheet.Range(Cells(RowCount + 12, Start_Month), Cells(RowCount + 12, Current_Month))
                    .SeriesCollection.NewSeries
                    .SeriesCollection(4).Name = Data_Sheet.Cells(RowCount + 13, 4).Value
                    .SeriesCollection(4).Values = Data_Sheet.Range(Cells(RowCount + 13, Start_Month), Cells(RowCount + 13, Current_Month))
                    .SeriesCollection(1).XValues = Data_Sheet.Range(Cells(6, Start_Month), Cells(6, Current_Month))
                    .HasTitle = True
                    .ChartTitle.Characters.Text = Product_Team(Product_Count) & " SPI/CPI Trend"
                    .Location Where:=xlLocationAsObject, Name:=Chart_Sheet
                    .Axes(xlCategory, xlPrimary).HasTitle = True
                    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
                    .Axes(xlValue, xlPrimary).HasTitle = True
                    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "SPI/CPI"
                    .HasDataTable = True
                    .DataTable.ShowLegendKey = True
                End With
                
                Product_Count = Product_Count + 1
                Exit Do
            End If
           RowCount = RowCount + 1
    Loop

Data_Sheet.Activate
Loop

 

[SkipVought]

Hi,

Did you try doing the chart on the sheet, using the Chart Wizard?

Did your turn on your macro recorder once you generated the chart correctly?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

Skip,

I recorded creating the chart. I did not include the titles..

I guess I could use the R1C1 notation but I would like to know what I am currently doing wrong...

Tom

    Sheets.Add
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("A1")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "='Data Sheet'!R6C45:R6C56"
    ActiveChart.SeriesCollection(1).Values = "='Data Sheet'!R30C45:R30C56"
    ActiveChart.SeriesCollection(1).Name = "='Data Sheet'!R30C4"
    ActiveChart.SeriesCollection(2).XValues = "='Data Sheet'!R6C45:R6C56"
    ActiveChart.SeriesCollection(2).Values = "='Data Sheet'!R31C45:R31C56"
    ActiveChart.SeriesCollection(2).Name = "=""CPIcurr"""
    ActiveChart.SeriesCollection(3).Values = "='Data Sheet'!R32C45:R32C56"
    ActiveChart.SeriesCollection(3).Name = "=""SPIcum"""
    ActiveChart.SeriesCollection(4).Values = "='Data Sheet'!R33C45:R33C56"
    ActiveChart.SeriesCollection(4).Name = "='Data Sheet'!R33C4"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"

 

[SkipVought]

I can't tell what you are doing with all those seriescollections inside a loop. Dosn't make any sense to me.

Check out this tool, though.

faq707-4594

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

Skip,

I want to make a chart for each Product. Previously, i got the total amount of products in a variable (Total_IPT).Second loop runs down the rows to find the product name. Once it finds the product name what my attempt is anyway is to add a chart, input the source data for each series, the x axis source range. It changes the series names correctly but does not input the series source data correctly (does nothing really).

What is also strange, at least to me, is that

                Set Chart_Sheet = ActiveSheet
                Chart_Sheet.Name = Product_Team(Product_Count)

does nothign as well.

 

[SkipVought]

Why make a chart for each product?

The way a pro does it is to make ONE chart and use a control to change the criteria on a FILTER in the source data.

Multiple charts. Multiple maintenance headaches.

If, however, ALL the charts need to be viewed simultaneously, its a different matter. Then you have to address the subsource and placement of the charts on a Sheet or ChartSheet.

Which is it?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

Actually, I apologize...

I had some error handling in there which is why I did not see the errors.

I deleted out the error handling and I am getting
Method of 'cells' of object_global failed once it hits the first seriescollection.values code...

 

[SkipVought]

Reference the sheet object in the cells object as well.

.SeriesCollection(1).Values = Data_Sheet.Range(Data_Sheet.Cells(RowCount + 10, Start_Month), Data_Sheet.Cells(RowCount + 10, Current_Month))

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

Skip -

Thanks for your help once again.To be honest, I do not have to make a chart for each product, it's just the logic I used. I guess I could create a combobox and once the value is changed it would trigger the macro to get the corresponding data?

Thanks again,
Tom