CODEWITHSUNDEEP
excelvba
Arkadi w
Arkadi w

Reputation: 89

VBA Named Range that contains offsets is fixed in chart

I have a dynamic table with values in column Y:

I create series1 in name manager with the formula =OFFSET('Sheet1'!$Y$16,0,0,COUNTA('Sheet1'!$Y$16:$Y$5012),1)

I want to plot this via a chart in VBA, so I do

cht.Chart.SetSourceData Source:="=series1"

but what I get in my graph is "='Sheet1'!$Y$16:$Y$43" because data stops in line 43

instead of

='static bbg data'!series1

So later if I add lines to my table in column Y, these won't be updated on the graph vs is I were to create my chart and add ='static bbg data'!series1 manually.

Could you help me to solve this?

Thank you for the help

Upvotes: 1

Views: 86

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57733

Add an update procedure

Place an update procedure into a module

Sub UpdateChartSourceData()
    With Worksheets("SheetWithChart")
        cht.Chart.SetSourceData Source:=.Range("series1")
    End With
End Sub

And put this in your sheet with the chart data

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("series1")) Is Nothing Then
        cht.Chart.SetSourceData Source:=Me.Range("series1")
    End If
End Sub

to update the chart everytime the data changes.

You might need to define/adjust cht in the code above.

Or use formatted tables

An easier way would be to use a formatted table for source data. And make the chart using that complete table as source. This way the chart automatically updates if the table gets added rows or columns.

Upvotes: 3

Related Questions