Auto update to a chart

[rjplante]

I have a chart on a page and the data set will change based on what the user selects and transfers via another macro I have. I have a chart on the page that displays the data. I have done some digging and added a macro to update the charts display. I want the presented chart data to fill in the size of the chart window on the page. I have included an example of my chart and macro code below. My code crashes on the line below:
'Sets new range for each series
ActiveChart.SeriesCollection.Values = rng

I get a Run-Time Error '1004' Application defined or object-defined error. I don't know how to get it to work and auto adjust the chart.


My code:

Sub Change_Chart_Range()

    Dim i As Integer
    Dim r As Integer
    Dim n As Integer
    Dim p1 As Integer
    Dim p2 As Integer
    Dim p3 As Integer
    Dim rng As Range
    Dim ax As Range
    Dim ch As ChartObject
    Dim ws As Worksheet
   
    Set ws = ActiveSheet
   
    ws.ChartObjects.Select
   
    'Cycles through each series
    For n = 1 To ActiveChart.SeriesCollection.Count Step 1
        r = 0

        'Finds the current range of the series and the axis
        For i = 1 To Len(ActiveChart.SeriesCollection(n).Formula) Step 1
            If Mid(ActiveChart.SeriesCollection(n).Formula, i, 1) = "," Then
                r = r + 1
                If r = 1 Then p1 = i + 1
                If r = 2 Then p2 = i
                If r = 3 Then p3 = i
            End If
        Next i


        'Defines new range
        Set rng = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p2 + 1, p3 - p2 - 1))
        Set rng = Range(rng, rng.Offset(0, 1))

        'Sets new range for each series
        ActiveChart.SeriesCollection(n).Values = rng
     
   
        'Updates axis
        Set ax = Range(Mid(ActiveChart.SeriesCollection(n).Formula, p1, p2 - p1))
        Set ax = Range(ax, ax.Offset(0, 1))
        ActiveChart.SeriesCollection(n).XValues = ax

    Next n
End Sub

Chart and dara range:

Machine Follow-Up Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2	JOB #	Start Date	End Date	Delay	Duration					Data Set:	Test 1
3	AAA	18-Jan-2020	22-Jan-2020		4
4	BBB	11-May-2020	15-May-2020		4
5	CCC	23-Jun-2020	25-Jun-2020		2
6	DDD	15-Jul-2020	17-Jul-2020		2
7	EEE	3-Aug-2020	5-Aug-2020		2
8	FFF	17-Aug-2020	21-Aug-2020		4
9	GGG	9-Sep-2020	11-Sep-2020		2
10	HHH	21-Oct-2020	23-Oct-2020		2
11	III	7-Dec-2020	11-Dec-2020		4
12	JJJ	4-Jan-2021	8-Jan-2021		4
13					0
14					0
15					0
16					0
17					0
18					0
19					0
20					0
21					0
22					0
23					0
24					0
25					0
26					0
27					0
28					0
29					0
30					0
31					0
32
33
Combo Gantt Chart

Cell Formulas
Range	Formula
E3:E31	E3	=DATEDIF(B3,C3,"d")+D3

Unfortunately my chart did not come through on the xl2bb transfer, so I have pasted it below.

Thanks for the help.

 

[pbornemeier]

That line fails because you are assigning a 2xN array to the Values (and XValues a bit farther down) when you should be assigning a 1xN vector.

What version of Excel are using? I am not familiar with an Excel 'Combo Gantt Chart'.

 

[rjplante]

I am using Excel 365 and Excel Combo Gantt Chart is just my name for the tab and what I am trying to accomplish. I have figured out how to modify the X axis to fit the max and min values, but I still do not know how to adjust the Y axis so that only the rows with data in them will be plotted on the chart.

 

[pbornemeier]

Please post a few of the series formulas for your chart.

You could delete all of the series on the current chart then modify this to add the series for each populated row:

Sub CreateGraphInVBA()
    'Much taken from http://peltiertech.com/Excel/Charts/InteractiveChartCreation.html
    
    Dim rOEERange As Range
    Dim rDateRange As Range
    Dim objChart As ChartObject
    
    Set rOEERange = ActiveSheet.Range("B1:B20")
    Set rDateRange = ActiveSheet.Range("A1:A20")
    
    Set objChart = ActiveSheet.ChartObjects(1)
    With objChart.Chart
        .SeriesCollection.Add (ActiveSheet.Range("A1"))
        .SeriesCollection(1).XValues = rDateRange
        .SeriesCollection(1).Values = rOEERange
        .PlotBy = xlColumns
        .ChartArea.AutoScaleFont = False
        .ChartType = xlBarStacked
        .HasTitle = True
        .ChartTitle.Characters.Text = "My Title"
        .ChartTitle.Font.Bold = True
        .ChartTitle.Font.Size = 12
    End With
    
    Set rOEERange = Nothing
    Set rDateRange = Nothing
    Set objChart = Nothing

End Sub