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
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)