VBA Chart Macro Range Using Union - Error

Question

I am trying to create a scatter chart using a macro that uses data in columns that are not adjacent. Once that is working, I would like to add more series to the chart, stepping across columns, but I can't get the first part to work. This is my code:

Sub Better_Chart()
   Dim chtChart As Chart
   Dim X As Integer
   Dim Y As Integer
   Dim Z As Integer

   'For X = Range("E1").Column To Range("K1").Column Step 6

    X = Range("E1").Column
    Z = Range("C1").Column
    Cells(14, X).Select
    Range(Selection, Selection.End(xlDown)).Select
    If Range("E14") <> Empty Then
    Y = Selection.Rows.Count + 13
    End If

 'Create a new chart.

 Set chtChart = Charts.Add
 Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:="Yield Data")
 With chtChart

 '.Name = ""

 .ChartType = xlXYScatter
 'Link to the source data range.
' .SetSourceData Source:=Sheets("Yield Data").Range(Cells(14, Z), Cells(Y, X)),
 .SetSourceData Source:=Sheets("Yield Data").Union(Range(Cells(14, X), Cells(Y, X)), Range(Cells(14, Z), Cells(Y, Z))), _
 PlotBy:=xlColumns

' Next Y

 'Next X

 .HasTitle = True
 .ChartTitle.Text = Range("H3")
 .Axes(xlCategory, xlPrimary).HasTitle = True
 .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Range("H5")
 .Axes(xlValue, xlPrimary).HasTitle = True
 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Range("H7")

 End With

End Sub

I get a debug error on the setSourceDate line. However, this code works perfectly, in that it selects the exact data I want to graph:

Public Sub Test_Union()
   Dim chtChart As Chart
   Dim X As Integer
   Dim Y As Integer
   Dim Z As Integer

   'For X = Range("E1").Column To Range("K1").Column Step 6
    X = Range("E1").Column
    Z = Range("C1").Column
    Cells(14, X).Select
    Range(Selection, Selection.End(xlDown)).Select

    If Range("E14") <> Empty Then
    Y = Selection.Rows.Count + 13
    End If

    'Range(Cells(14, Z), Cells(Y, X)).Select
     Union(Range(Cells(14, Z), Cells(Y, Z)), Range(Cells(14, X), Cells(Y, X))).Select
    'Range(Cells(14, Z), Cells(Y, X)).Select

' Next Y

 'Next X  

End Sub

Can anyone see what I am doing wrong?

Mathieu Guindon · Answer 1 · 2019-12-13T22:04:11.747

.SetSourceData Sheets("Yield Data").Union(Range(Cells(14, X), ...

If Sheets("Yield Data") isn't the ActiveSheet, then all these unqualified Range and Cells calls inside that Union-from-a-very-specific-sheet call are trying to union ranges from different worksheets, which blows up with error 1004.

The best solution is to qualify all references to the sheet you mean to be working with:

Set sh = ActiveWorkbook.Worksheets("Yield Data")
.SetSourceData sh.Union(sh.Range(sh.Cells(14, X), ...

Another "solution" (work-around, really) would be to make that sheet the ActiveSheet:

ActiveWorkbook.Worksheets("Yield Data").Activate
.SetSourceData Union(Range(Cells(14, X), ...

But to write reliable code, you really should be avoiding Select and Activate, so go with properly-qualified Worksheet member calls - including with your "works fine" code, which is also implicitly referring to whatever the ActiveSheet is, but only involves that sheet.

VBA Chart Macro Range Using Union - Error

1 Answers1

Linked