Search code examples
vbaexcelexcel-charts

Multiple line using named ranges VBA


I am attempting to plot multiple named ranges on to a chart. These named ranges (named for example Time_Series_1) are of the form:
=INDIRECT(Ranges!$AJ$3) where this argument Ranges!$AJ$3 is for example

 ="'Source Data'!"&ADDRESS(N3,(Comparisons!$G$28+5))&":"&ADDRESS(N3(Comparisons!$I$28+5))

The code I'm attempting to use to plot the these ranges (I have started with one as any more remain to be impossible as one is not working) is:

Sheets("Comparisons").Select
Sheet10.ChartObjects("Chart 2").Activate

Set Rng = range("Time_Series_1")
Set Date_Rng = range("Time_Series_Dates_1")

With ActiveChart
    .SetSourceData Source:=Rng
    .XValues = Date_Rng
End With

It's not working! The Chart remains blank. Completely empty. What am I not updating? What am I not activating?

Thanks


Solution

  • I have no way of testing your model, but try this:

    Dim Chart_Sheet As Worksheet
    Dim My_Chart As ChartObject
    Dim Rng As Range
    Dim Date_Rng As Range
    
    Set Chart_Sheet = ThisWorkbook.Sheets("Comparisons")
    Set My_Chart = Chart_Sheet.ChartObjects("Chart 2")
    Set Rng = Range("Time_Series_1")
    Set Date_Rng = Range("Time_Series_Dates_1")
    
    With My_Chart.Chart
        .SetSourceData Source:=Rng
        .SeriesCollection(1).XValues = Date_Rng
    End With
    

    (I am assuming that your Indirect reference works properly)