One of 3 series won't change though all code is the same.

[aMember]

Hi All,

Can anyone see what is wrong with this code? Series PT1 and PT2 update fine all the time but BAC always fails?

No errors are given, it just pops out of this function. No error handlers on. Verified that the named ranges are pointing to the correct places.

I'm at a loss.

Thanks in advance for any help/suggestions.
Andrea

Function MoveSeries()
'=======================================================
'=======================================================
'Range("Date&quot is a row of dates on a Data Sheet
'
'FocusMonth is set elsewhere. In a 'window' of 6
'dates FocusMonth is the 4th month. Ex:
'Jan-02 Feb-02 Mar-02 Apr-02 May-02 Jun-02
'FocusMonth is Apr02 in this case. Anything after is
'in the future, before that, in the past. (e.g. Apr-02 is the current month reporting)
'These dates are pulled from Sheets("Health&quotand
'any number of dates can exist.
'Pulling the dates from Sheets("Health&quot into this window of 6
'allows it to act like a sliding window so it
'can slide backwards in time or look further ahead
'than the default 2 months and displays
'corresponding data from the Data sheet.

'Purpose of this procedure is to move series in a chart
'so that the data in the chart moves with the data
'as FocusMonth changes.
'=======================================================
'=======================================================

'Get the column of the oldest month - subtract
'3 because the oldest month is 3 months before
'the focus month
OldestMonthCol = WorksheetFunction.Match(FocusMonth, Range("Date&quot, 0) - 3

'Since we always display 6 in the window, the newest
'month 5 more than Oldest
NewestMonthCol = OldestMonthCol + 5

ChartName = Worksheets("Quad Chart&quot.ChartObjects(1).Name
ActiveSheet.ChartObjects(ChartName).Activate

Dim DataRange As Range
Dim WindowRange As Range
Dim DataSheet As Worksheet
Set DataSheet = Sheets("Data&quot
With DataSheet

'Create a "window" of 6 dates
Set WindowRange = .Range(.Cells(1, OldestMonthCol).Address, .Cells(1, NewestMonthCol).Address)

'Loop through all the series and change them
For i = 1 To ActiveChart.SeriesCollection.Count
Select Case i
Case 1
theRow = .Range("PT1&quot.Row
theName = "PT1"
Case 2
theRow = .Range("PT2&quot.Row
theName = "PT2"
Case 3
theRow = .Range("BAC&quot.Row
theName = "BAC"
End Select
'set the range for the window of dates in view
Set DataRange = .Range(.Cells(theRow, OldestMonthCol).Address, .Cells(theRow, NewestMonthCol).Address)
ActiveChart.SeriesCollection(theName).XValues = WindowRange
ActiveChart.SeriesCollection(theName).Values = DataRange
Next i
End With

end function

 

[RobBroekhuis]

Have you put a breakpoint on the

ActiveChart.SeriesCollection(theName).Values = DataRange

statement? That would allow you to check some things in the immediate window, such as

1) Does the code get to the statement three times?
2) Does activechart.seriescollection(theName) exist in all three cases?
3) is the datarange.address set correctly for all three cases?

Rob

 

[aMember]

The first 2 cases work perfectly. I have examined them in the debugger as well as on my spreadsheet in that I get the data that I expect.

Did not put a breakpoint on the line you suggested but on :

ActiveChart.SeriesCollection(theName).XValues = WindowRange and the range is correct...data is valid.

On case #3, step past this line and it jumps out of the routine.

Thanks for the suggestions. Anything else?

 

[RobBroekhuis]

When it stops the third time, what is the response, in the immediate window, to

?ActiveChart.SeriesCollection(theName).name

if it comes back with the right name, how about

xv=activechart.seriescollection(theName).xvalues
?ubound(xv)

Just some diagnostics. Your problem sounds strange, so I'm just trying to probe...
Rob

 

[SkipVought]

Andrea,

I took your code and created the workbook to test your code.

Everything works fine!

You might check in your Chart that the BAC series is named correctly and the values and X-axis are all correctly defined. Skip,
SkipAndMary1017@mindspring.com

 

[aMember]

Out of desperation, deleted the series, and recreated them and things worked fine. Strange enough, copied the series, pasted them to a text file, deleted them in Excel, then copied them back in when I was created them. For some reason, that worked.

Thanks to all for your suggestions.