0

I'm trying to use VBA to change the series of several charts to use dynamic ranges instead of static cell ranges. Each time a get a vague 1004 application error.

Here's my code:

Sub test20()
    Dim wks As Worksheet, tbl As ListObject
    Dim arr()

    arr = [{"Date","1/1/2016","2/1/2016","3/1/2016","4/1/2016","5/1/2016","6/1/2016";"Green",100,200,300,400,350,425;"Yellow",350,250,150,50,75,125;"Red",10,7,5,3,25,45}]
    Set wks = ActiveSheet
    wks.Range("a1:D7") = WorksheetFunction.Transpose(arr)

    Set tbl = wks.ListObjects.Add(xlSrcRange, wks.Range("a1").CurrentRegion, , xlYes)
    tbl.Name = "tblTix"

    'Dynamic ranges
    ActiveWorkbook.Names.Add Name:="rngLbl_1", RefersToR1C1:="=OFFSET(tblTix,MATCH(MAX(tblTix[Date]),tblTix[Date],1)-3,MATCH(""Date"",tblTix[#Headers],0)-1,1,1)"
    ActiveWorkbook.Names.Add Name:="rngLbl_2", RefersToR1C1:="=OFFSET(tblTix,MATCH(MAX(tblTix[Date]),tblTix[Date],1)-2,MATCH(""Date"",tblTix[#Headers],0)-1,1,1)"
    ActiveWorkbook.Names.Add Name:="rngLbl_3", RefersToR1C1:="=OFFSET(tblTix,MATCH(MAX(tblTix[Date]),tblTix[Date],1)-1,MATCH(""Date"",tblTix[#Headers],0)-1,1,1)"

    ActiveWorkbook.Names.Add Name:="rngDat_1", RefersToR1C1:="=OFFSET(tblTix,MATCH(MAX(tblTix[Date]),tblTix[Date],0)-3,1,1,3)"
    ActiveWorkbook.Names.Add Name:="rngDat_2", RefersToR1C1:="=OFFSET(tblTix,MATCH(MAX(tblTix[Date]),tblTix[Date],0)-2,1,1,3)"
    ActiveWorkbook.Names.Add Name:="rngDat_3", RefersToR1C1:="=OFFSET(tblTix,MATCH(MAX(tblTix[Date]),tblTix[Date],0)-1,1,1,3)"

    'Static ranges to test, just in case
    ActiveWorkbook.Names.Add Name:="rngStatLbl", RefersTo:="=Sheet1!$B$1:$D$1"
    ActiveWorkbook.Names.Add Name:="rngStatData", RefersTo:="=Sheet1!$B$3:$D$3"

    Range("A1").Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$D$1,Sheet1!$A$3:$D$5")
    MsgBox "Modify the chart > Select Data > Switch Row Column", vbOKOnly
    Stop

    ActiveChart.SeriesCollection(1).Formula = "=SERIES(Sheet1!$B$1,Book1.xlsx!rngLbl_1,Book1.xlsx!rngDat_1,1)"

    'This value was pulled using VBA after the change was made in the GUI
    ActiveChart.SeriesCollection(1).Formula = "=SERIES(Book1.xlsx!rngLbl_1,Sheet1!$B$1:$D$1,Book1.xlsx!rngDat_1,1)"



End Sub

Any ideas how to get around this? After getting an answer to this question, I've tried

...SeriesCollection(1).Formula = ....
...SeriesCollection(1).FormulaR1C1 = ....

but still no luck.

Community
  • 1
  • 1
MJA
  • 350
  • 1
  • 3
  • 15
  • what i have done, make chart link to a specific Range, in that range contains formula will reflect different result when i filter something. or using macro to update data in that Range. The chart will automatically reflected. :) – Eric K. Aug 06 '16 at 02:11
  • i tried using Named Range, although the named range is dynamic using offset formula. but once i applied the named range into Chart, the chart only capture the Range address, it didn't dynamically change. guess was limitation for excel chart. – Eric K. Aug 06 '16 at 02:16
  • if you are trying to use dynamic range as active data for `SeriesCollection` then you need to use the `SeriesCollection.XValues` and `SeriesCollection.Values` – Shai Rado Aug 06 '16 at 03:52
  • If u take the chart inputs from an excel table the chart series will automatically resize with the table length – Mats Lind Aug 06 '16 at 09:25
  • Thank you all for you comments. @MLind - I don't want the chart series to add unlimited data; I only want the last 3 (or in some cases 12) periods. – MJA Aug 08 '16 at 19:16
  • @EricK. - I wonder if this could be a limitation of the Excel version. This works for me in Excel 2013 but the end user has Excel 2010 and I haven't tested full there. Which version are you using? I was hoping to avoid using another coded procedure but that might be the way I have to go. – MJA Aug 08 '16 at 19:20
  • @ShaiRado - I'll look into using .XValues and .Values. Thanks for the suggestion. – MJA Aug 08 '16 at 19:20
  • im using 2010 version – Eric K. Aug 08 '16 at 23:36

0 Answers0