Refreshing a seriescollection using VBA

[hrm1220]

I've been looking through the threads and haven't found the solution to my problem. I'm trying to make this work were I don't have to add the offset formula in the data range, my users wouldn't know how to do this. I'm trying to automate the refresh of the seriescollection of the charts. There are about 10-12 charts that need to have the added column that is updated every quarter.

Here's what I have so far and I'm hoping someone can point me to my errors:

Private Sub CommandButton1_Click()

    Dim lstCol As Integer
    Dim xlrow1 As Integer, xlrow2 As Integer
    'Dim newrng As Range
    Sheets("cost and space").Select
    datasheet = ActiveSheet.Name
    msg = InputBox("What's the quarter to start with?", , "Q3'01")
    Range("A1").Select
    Cells.Find(What:=msg, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
        startcol = ActiveCell.Column
    
    'Find the last added column
    Do
        ActiveCell.Offset(0, 1).Select
    Loop Until IsEmpty(ActiveCell)
    
    lstCol = ActiveCell.Column - 1
    
Dim sh
Dim myChart As Chart

For i = 0 To Worksheets.Count
For Each sh In ActiveSheet.ChartObjects
     sh.Select
    For j = 1 To ActiveChart.SeriesCollection.Count
    With ActiveChart
    With .SeriesCollection(j)
    myrange = .Formula
    myrange1 = Split(myrange, ":")(0)
    myrangeend = Split(Split(myrange, "!")(0), "(")(1)
    startnum = Application.Find(":", myrange, 1) + 1
    myrange2 = Mid(myrange, startnum, 6)
    myrange2 = Split(myrange2, ",")(0)
    xlrow = Split(Split(myrange2, "$")(2), ",")(0)
    xlrow1 = xlrow
    myrange3 = Right(myrange, Application.Find(datasheet & "'!", myrange, startnum) - startnum - 2)
    If IsError(Application.Find("$", myrange3, 1)) Then
    xlrow = Split(myrange3, ",")(0)
    Else: xlrow = Split(Split(myrange3, "$")(1), ",")(0)
    End If
    xlrow2 = xlrow
    newrng = myrange1 & ":" & Cells(xlrow1, lstCol).Address & myrangeend & "!" & Cells(xlrow2, startcol).Address _
    & ":" & Cells(xlrow2, lstCol).Address & Split(myrange3, xlrow2)(1)
    End With
    With .SeriesCollection(j)
    .Values = myrange1 & ":" & Cells(xlrow1, lstCol).Address & myrangeend & "!" & Cells(xlrow2, startcol).Address _
    & ":" & Cells(xlrow2, lstCol).Address & Split(myrange3, xlrow2)(1)
    
    End With
    End With
    Next
Next
i = i + 1
ActiveSheet.Next.Select
Next

Thanks for your time and help looking through my messy code

 

[SkipVought]

Hi,

I don't understand what YOU are doing as opposed to your USERS.

If YOU set up the OFFSET functions, then your users don't have to know anything about it, just like writing a macro that your users don't know anything about.

So what's all this coding for? Just to reference the correct ranges? WAAAAAAAYYYYY to complicated!

I just did a chart this morning to help us schedule the cooking and food prep tasks leading up to Thanksgiving Dinner. To catch the range changes, I used a simple

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.DisplayAlerts = False
    Target.CurrentRegion.CreateNames True, False, False, False
    Application.DisplayAlerts = True
End Sub

then all my Chart Series are referenced like this...
[tt]
='2006 Thanksgiving Menu.xls'!Start
[/tt]
that's ALL the code I've used!

Skip,

_[red][/red]

 

[hrm1220]

that works GREAT for part of the first graph. Which I greatly appreciate. I'm know to make things more complicated then need be. I have about four + different graphs on mulitple sheets. The data is on just one sheet.

Also, how do I update the X axis label?
How do I update all the graphs in the file? That was why I was doing the For...Next in the code.
And for some reason it missed updating the first data point of the first graph.

Thanks again for all your help and quick response

 

[hrm1220]

sorry, I was wrong. The code you provided wasn't updating the graph. I had run my code and that's why it was updating part of the graph. I appologize for any inconveniences.

 

[SkipVought]

How did you use the information that I posted?

Here are the steps.

1) paste the code in the SHEET OBJECT code window. This code assumes that a CHANGE has been made to the SOURCE DATA range.

2) edit each Chart Source Data - Series Tab for each series including the category axis labels, changing the Series Reference from...
[tt]
=some stuff here!range reference
[/tt]
to...
[tt]
=some stuff here!YourNewRangeName
[/tt]
Now if yo did ALL that for each chart, I'd be surprized, because it would have WORKED!

Skip,

_[red][/red]

 

[SkipVought]

Oh yes...

it ALSO assumes that your Source Data Area is configured as a TABLE and ISOLATED from ALL other data as a table should be. See Excel Help for the proper definition of a TABLE if necessary.

Skip,

_[red][/red]

 

[hrm1220]

Skip,

I apologize but I'm not quite understanding the "YourNewRangeName". Are you stating I need to define name ranges in the data table?

thanks again for your patience

 

[SkipVought]

"Are you stating I need to define name ranges in the data table?"

Yes! That's all that my CODE does, assuming that you have a TABLE with unique headings. Then the RangeName will ALWAYS deinfe the range regardless of additions or deletions of rows in the table. So using the RangeName to reference the SERIES means that your series ALWAYS reflects the actual data.

How can I rename a table as it changes size faq68-1331

Skip,

_[red][/red]

 

[hrm1220]

Skip,

Thanks for your help and patience with me. It took me a while to update all the data series with name ranges. Now that's done, my next question is how do I add new data w/a different starting point and still you the historical data w/the new data. Without having to add more name ranges to the historical data.

I just wanted your point of view, since I tend to make things more complicated.

Thanks

 

[SkipVought]

Could you please explain what you mean by "different starting point"?

Have you tried FILTERING your data?

Skip,

_[red][/red]

 

[hrm1220]

sorry. I hope this isn't too lengthy.
For example I have a table of dogs, cats, birds, etc w/ and w/o shots and the data point starts in Q1'04. Let's say I want to add another category, let's say hamsters, in a totally new graph w/the starting point of the data series to be Q1'05 and want to include the data of dogs, cats, and birds starting at Q1'05.
I still want to maintain the other graphs that have dogs, cats, birds with the starting poing of Q1'04.
Here's an example of the data table:

Q104 Q204 Q304 Q404 Q105 Q205 Q305 Q405 Q106
Cat w/shots 50 60 70 80 90 100 110 120 130
cat w/o shot 100 110 120 130 140 150 160 170 180
Dog w/shots 35 45 55 65 75 85 95 105 115
Dog w/o shot 10 20 30 40 50 60 70 80 90
Bird w/shots 55 65 75 85 95 105 115 125 135
Bird w/o shot 30 40 50 60 70 80 90 100 110
hampsters w/shots 10 20 30 40 50 60 70 80 90
hampsters w/o shot 7 17 27 37 47 57 67 77 87

So the question is, do I need to have two different name ranges for dogs, cats, and birds for the different starting points (Q1'04 and Q1'05) for the different graphs?

Thanks

 

[SkipVought]

please state the range that your data resides.

please state the CATEGORY range

please state any one of the VALUE ranges

Skip,

_[red][/red]

 

[hrm1220]

range of data = cells M2:V10

xvalues= cells N2:V2

for cat w/shot range = cells M3 for name and N3:V3 for data

now for hamster graph:

range of data = cells R2:V10
xvalues= cells R2:V2
for cat w/shot range = cells M3 for name and R3:V3 for data

 

[SkipVought]

You threw a bit of a curve, but here's the code for the SECOND chart...

Sub SetRange()
    Dim rCat As Range, ser As Series, i As Integer
    Set rCat = Cells.Find("Q105")
    Set rCat = Range(rCat, rCat.End(xlToRight))
    
    Application.DisplayAlerts = False
    ActiveSheet.[m2].CurrentRegion.CreateNames True, True, False, False
    Application.DisplayAlerts = True
    
    i = 1
    With ActiveSheet.ChartObjects(2).Chart  'first chart has others, second chart has birds
        For Each ser In .SeriesCollection
            ser.XValues = rCat
            Select Case i
                Case 1
                    ser.Values = Application.Intersect(rCat.EntireColumn, [Bird_w_shots].EntireRow)
                Case 2
                    ser.Values = Application.Intersect(rCat.EntireColumn, [Bird_w_o_shots].EntireRow)
            End Select
            i = i + 1
        Next
    End With
End Sub

Skip,

_[red][/red]

 

[hrm1220]

Skip,

I just have one more question, then I'm hoping I'll be done with this. On the "[m2]" you provided above, how can I make that not specific to m2, in case it may change?

I tried the following:

Sheets("cost and space").Select
datasheet = ActiveSheet.Name
msg = InputBox("What's the quarter to start with?", , "Q3'01")
Range("A1").Select
Cells.Find(What:=msg, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
startcol = ActiveCell.Column
start=activecell.address
Set rng = msg
Set rng = Range(rng, rng.End(xlToRight))

..............
ActiveSheet.[start].CurrentRegion.CreateNames True, True, False, False

but of course VBA didn't like that.

 

[SkipVought]

    Sheets("cost and space").Select
    datasheet = ActiveSheet.Name
    msg = InputBox("What's the quarter to start with?", , "Q3'01")
    Set rng = Cells.Find(What:=msg)
    If Not rng Is Nothing Then
        Set rng = Range(rng, rng.End(xlToRight))
    
        rng.CurrentRegion.CreateNames True, True, False, False
        
    End If

Skip,

_[red][/red]