Creating a chart with "Union"

[kotzo]

Hi,

I want to create a chart in a specific sheet ("Problematic Stock") of a workbook ("Reports_1.xls"). I take the data from the workbook ( backbone = "Data_Backbone.xls") from the sheet "Problematic Stock".

I use the following code:

  [SIZE=1]Workbooks.Open "C:\Logistics Container\Reports_1.xls"
    Workbooks("Reports_1.xls").Activate
  Worksheets("Problematic Stock").Activate[/SIZE]
[SIZE=1]   With Charts.Add
      .Name = "Test"
      .ChartType = xlColumnClustered
    [B]  .SetSourceData Source:=Workbooks(backbone).Sheet("Data_Discont").Union(range(cells(4, 1), cells(inde + 3, 1)), range(cells(4, 12), cells(inde + 3, 12))), PlotBy:=xlRows
[/B]      .HasTitle = True
      .ChartTitle.Text = "=Problematic Stock!R1C2"
      .Axes(xlCategory, xlPrimary).HasTitle = True
      .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
      .Axes(xlValue, xlPrimary).HasTitle = True
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
   End With[/SIZE]

Error: 438 (at the bold line).

Could you please help me ?

Thanks,

Tzovanis

 

[Andrew Poulsom]

The Union method is a member of the Application object. And you need to qualify the range and cells properties (otherwise the active sheet is used). Try the untested:

Dim Sh As Worksheet
Set Sh = Workbooks(backbone).Sheet("Data_Discont")
.SetSourceData Source:=Application.Union(Sh.Range(Sh.Cells(4, 1), Sh,Cells(inde + 3, 1)), Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12))), PlotBy:=xlRows

 

[kotzo]

Hi,

same error: "Object doesn't support this property or method"

[SIZE=1]Workbooks.Open "C:\Logistics Container\Reports_1.xls"
    Workbooks("Reports_1.xls").Activate
  Worksheets("Problematic Stock").Activate
Set Sh = Workbooks(backbone).Sheet("Data_Discont")
   With Charts.Add
      .Name = "Test"
      .ChartType = xlColumnClustered
      .SetSourceData Source:=Application.Union(Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)), Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12))), PlotBy:=xlRows
      .HasTitle = True
      .ChartTitle.Text = "=Problematic Stock!R1C2"
      .Axes(xlCategory, xlPrimary).HasTitle = True
      .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
      .Axes(xlValue, xlPrimary).HasTitle = True
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
   End With[/SIZE]

 

[Andrew Poulsom]

This:

Set Sh = Workbooks(backbone).Sheet("Data_Discont")

should be:

Set Sh = Workbooks(backbone).Sheets("Data_Discont")

 

[kotzo]

It worked.

Thank you.

Another question:

Is this code:

.SetSourceData Source:=Application.Union(Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)), Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12))), PlotBy:=xlRows

equivalent with this ?

.SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1))
.SeriesCollection(1).Values = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12))

The problem is that the second alternative that I want to use is not working.

 

[Andrew Poulsom]

Try:

.SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1).Address(, , xlR1C1))
.SeriesCollection(1).Values = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12).Address(, , xlR1C1))

You may also need single quotes around the workbook name.

 

[kotzo]

Hi,

"backbone" is a string (see the code)

The code is still not working (same error)

[SIZE=1]Dim backbone As String[/SIZE]
[SIZE=1][/SIZE] 
[SIZE=1]Workbooks.Open "C:\Logistics Container\Reports_1.xls"
    Workbooks("Reports_1.xls").Activate
  Worksheets("Problematic Stock").Activate
Set Sh = Workbooks(backbone).Sheets("Data_Discont")
   With Charts.Add[/SIZE]
[SIZE=1][/SIZE] 
[SIZE=1].SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1).Address(, , xlR1C1))
.SeriesCollection(1).Values = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12).Address(, , xlR1C1))
      .HasTitle = True
       .HasLegend = False
       .ChartTitle.Text = "Discontinued Products"
      .Axes(xlCategory, xlPrimary).HasTitle = False
      .Axes(xlValue, xlPrimary).HasTitle = True
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Value"
      .Location Where:=xlLocationAsObject, Name:="Problematic Stock"
    
   End With[/SIZE]

error 1004


Regards,

Tzovanis

 

[Andrew Poulsom]

Where are you assigning a value to the backbone variable?

 

[kotzo]

The value of backbone is correct (I traced it)

  iReply = MsgBox(PROMPT:="Do you wish to run CY ?", _
            Buttons:=vbYesNoCancel, Title:="Greece Or Cyprus")
  
    If iReply = vbYes Then
     Workbooks.Open "C:\Logistics Container\Data_Backbone_CY.xls"
     backbone = "Data_Backbone_CY.xls"
    ElseIf iReply = vbNo Then
   Workbooks.Open "C:\Logistics Container\Data_Backbone.xls"
    backbone = "Data_Backbone.xls"
    Else
        Exit Sub
    End If

 

[kotzo]

Hi,

I tried to trace the values of the code and found the following things:

1) Sh.Range(Sh.Cells(4, 1), shows the date value, but Sh.Cells(inde + 3, 1) shows no value when I place the cursor above the code

2) "xlR1C1" gives me the strange value of -4150