Problem When Creating A Multiple Series Chart

[kotzo]

Hi,

The following code creates 2 charts ("Problematic" and "Problematic_10")
in the worksheet "Problematic Stock" of the workbook "Reports_1"

Everything work for the first chart. The problem starts with the command

ActiveSheet.ChartObjects("Problematic_10").Activate

Run-time error "1004"

(see bold command in code bellow)

I want to mention that the same command some lines above for the chart "Problematic" works perfectly

[SIZE=1]  ' *************************************************************************************************
  '                                             CREATION OF SUM CHART
 ' **************************************************************************************************
  Workbooks.Open "C:\Logistics Container\Reports_1.xls"
    Workbooks("Reports_1.xls").Activate
  Worksheets("Problematic Stock").Activate
Set Sh = Workbooks(backbone).Sheets("Data_Discont")[/SIZE]
[SIZE=1]If inde <= 1 Then
   With ActiveSheet.ChartObjects.Add(Left:=25, Width:=375, Top:=50, Height:=225)
      .Name = "Problematic"
      .Chart.ChartType = xlLineMarkers
      .Chart.SeriesCollection.NewSeries
       .Chart.SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)).Address(, , xlR1C1)
        .Chart.SeriesCollection(1).Values = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12)).Address(, , xlR1C1)
      .Chart.HasTitle = True
       .Chart.HasLegend = False
       .Chart.ChartTitle.Text = "Discontinued Products - Sum"
      .Chart.Axes(xlCategory, xlPrimary).HasTitle = False
      .Chart.Axes(xlValue, xlPrimary).HasTitle = True
      .Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Áîßá"[/SIZE]
[SIZE=1]    GoTo 9993
   End With
  
End If[/SIZE]
[SIZE=1]9993
 
ActiveSheet.ChartObjects("Problematic").Activate
 With ActiveSheet.ChartObjects("Problematic")
        .Chart.SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)).Address(, , xlR1C1)
        .Chart.SeriesCollection(1).Values = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12)).Address(, , xlR1C1)[/SIZE]
[SIZE=1]   End With[/SIZE]

[SIZE=1]  ' *************************************************************************************************
  '                                             CREATION OF "PER PRODUCT" CHART
 ' **************************************************************************************************[/SIZE]
[SIZE=1]If inde <= 1 Then
   With ActiveSheet.ChartObjects.Add(Left:=25, Width:=375, Top:=300, Height:=225)
      .Name = "Problematic_10"
      .Chart.ChartType = xlLineMarkers
      .Chart.SeriesCollection.NewSeries
      
      For icolumn = 1 To 1
      .Chart.SeriesCollection(icolumn).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)).Address(, , xlR1C1)
      .Chart.SeriesCollection(icolumn).Values = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 2), Sh.Cells(inde + 3, 11)).Address(, , xlR1C1)
      .Name = icolumn
      Next
      
      .Chart.HasTitle = True
       .Chart.HasLegend = True
       .Chart.ChartTitle.Text = "Discontinued Products / Per Product"
      .Chart.Axes(xlCategory, xlPrimary).HasTitle = False
      .Chart.Axes(xlValue, xlPrimary).HasTitle = True
      .Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Áîßá"
'
    GoTo 99931
   End With
  
End If[/SIZE]
[SIZE=1]99931
 
If inde <= 1 Then GoTo 44333[/SIZE]
[SIZE=1][B]ActiveSheet.ChartObjects("Problematic_10").Activate[/B]
 With ActiveSheet.ChartObjects("Problematic_10")
     
For icolumn = 1 To 10
        .Chart.SeriesCollection(icolumn).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)).Address(, , xlR1C1)[/SIZE]
[SIZE=1]        .Chart.SeriesCollection(icolumn).Values = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, icolumn + 1), Sh.Cells(inde + 3, 12)).Address(, , xlR1C1)
Next icolumn
44333
   End With[/SIZE]

Thank yoy for your help,

Tzovanis

 

[Andrew Poulsom]

In fact, looking at your code, it seems you are changing the name of the second chart when you add the series

Pedro, you have eyes like a hawk! I imagine kotzo wanted to name the Series:

.Chart.SeriesCollection(icolumn).Name = icolumn

 

[pgc01]

Hi Andrew

Pedro, you have eyes like a hawk!

... unfortunately most of the time is more eyes like a mole.