I am trying to write a macro that will create a new column chart which only use as many rows of data as available as its datasource i.e. dynamic named ranges.
However each time I get an error saying "unable to set values properties of a series class"
Code
Dim rng As Range, val As Range
Set rng = "=OFFSET(My_Portfolio!$A$3,0,0,COUNTA(My_Portfolio!$A:$A)-1,1)"
Set val = "=OFFSET(My_Portfolio!$N$3,0,0,COUNTA(My_Portfolio!$N:$N)-1,1)"
Charts.Add
ActiveChart.ChartType = xl3DColumnClustered
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = rng
ActiveChart.SeriesCollection(1).Values = val
ActiveChart.SeriesCollection(1).Name = "=""Current Worth"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="My_Portfolio"
Display More
Can someone please advise me how best to do this?
THanks