Excel VBA charts - how to set row and column labels

Question

I'm trying to follow along this tutorial:

http://www.homeandlearn.org/vba_charts_sheets.html

Here is as far as I've gotten:

Sub MakeChart()
    Dim ChartSheet1 As Chart
    Set ChartSheet1 = Charts.Add
    With ChartSheet1
        .SetSourceData Source:=Sheets("clc_hgn_hgn").Range("C3:AO41")
    End With
End Sub

The problem is that Excel automatically assumes the column and row headers are in the data source, when in my worksheet they are not. How do I get Excel to retrieve the headers from different ranges?

For instance, in my worksheet the data is contained in the range C3:AO41, but the horizontal top headers are in C1:AO1 and the vertical left headers are in A3:A41.

Answer 1

If your series names and X axis labels are not the column/row headers of the data set, then you cannot use the SetSourceData method. Instead, you need to add each series individually and add a command that specifies the X axis (Category) labels.

For each series do something along the lines of

ActiveChart.FullSeriesCollection(1).Name = "=Sheet5!$A$3"
ActiveChart.FullSeriesCollection(1).Values = "=Sheet5!$C$3:$F$3"

And for the X axis categories something like

ActiveChart.FullSeriesCollection(1).XValues = "=Sheet5!$C$1:$F$1"

Modify to suit your object names and wrap in a WITH statement as desired.

Answer 2

Sub MakeChart()
    Dim rData As Range
    Dim ChartSheet1 As Chart

    With Sheets("clc_hgn_hgn")
        Set rData = Union(.Range("A1"), .Range("C1:AO1"), _
            .Range("A3:A41"), .Range("C3:AO41"))
    End With

    Set ChartSheet1 = Charts.Add

    With ChartSheet1
        .SetSourceData Source:=rData
    End With

End Sub