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]

Not an answer to your question, but why do you need to activate it?

 

[kotzo]

I am new in vba.

In order to process things in a chart shouldn't I activate everytime the
relevant chart ?

If this is correct, then the first activated chart in the code is the "Problematic". So after the processing of this chart, I must activate the "Problematic_10" in order to make the changes in this chart.

Is this not necessary ? Is there another alternative ?

And even if this is not necessary, why this error ?

Thanks,

Tzovanis

 

[Andrew Poulsom]

No it's not necessary because you have:

With ActiveSheet.ChartObjects("Problematic_10")

Sorry, I don't know the cause of the error.

 

[kotzo]

Hi,

I deleted the line

ActiveSheet.ChartObjects("Problematic_10").Activate

and still the same error.

Run-time error "1004"

 

[Andrew Poulsom]

On which line do you get that error?

 

[kotzo]

ActiveSheet.ChartObjects("Problematic_10").Activate

 

[kotzo]

sorry,


With ActiveSheet.ChartObjects("Problematic_10")

 

[pgc01]

Hi Kotzo

The error seems to say that there is no chartobject named "Problematic_10".

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

      .Name = icolumn

After the code runs, and stops at the error, go to the worksheet and check the name of the second chartobject.

 

[kotzo]

Hi pgc01,


It worked !!!

I thank you and Andrew Poulsom for your help and support !!!

Tzovanis