Hi
Can anyone point me in the right direction on working with Charts in Excel. I have a workbork with five pages, each page has many charts, each chart has three series. I had specified a range well beyond the data, the blank cells did not appear on the charts, and the charts updated automatically as data was entered. The data page has changed, now I need to set the ranges each time.
I know the final column for each series, how can I scroll through each chart on each sheet, and update the column setting to the new requirement. I know how to do this by selecting each Chart by name, and setting the range by using the full range details. But I am sure there must be an easier way.
Changing chart series
Dieterdp
I got there in the end by help text; trial and error; and Google. I put the below into a module:
Public Sub ChartUpdate(xlSheet As Worksheet, StrColumn As String)
Dim c, x, s, j, k, lngLastDollar, lngFirstDollar As Long
Dim r As String
c = xlSheet.ChartObjects.Count
If c > 0 Then
For x = 1 To c
xlSheet.ChartObjects(x).Activate
s = ActiveChart.SeriesCollection.Count
If s > 0 Then
For j = 1 To s
r = ActiveChart.SeriesCollection(j).Formula
lngLastDollar = 0
lngFirstDollar = 0
For k = 1 To Len(r)
If Mid$(r, k, 1) = "$" Then
lngFirstDollar = lngLastDollar
lngLastDollar = k
End If
Next k
r = Left$(r, lngFirstDollar) & StrColumn & Right$(r, Len(r) - lngLastDollar)
ActiveChart.SeriesCollection(j).Formula = r
Next
End If
Next
End If
End Sub
This works because my ranges have $s in them e.g. =SERIES(Data!$C$15:$C$15,Data!$D$3:$DJ$3,Data!$D$79:$DJ$79,3). I call this from a button which calls the function for each sheet, and supplies the latest required column using its letter reference.
Its not elegant but it works.