Searching for a Chart Series by Series Name

[Cyberdude]

I have a macro that will search for a chart's line Series with the name "Today". It works OK if there are actual lines being plotted on the chart, but if the chart is empty, then it crashes. You might think of such a chart as a template that has up to 9 lines "defined" ... you'll see the definitions if you look at the Source Data dialog box. If I provide the data in the correct location, then lines WILL be plotted. But at the moment there is no data in the defined data locations.

When it crashes I get "1004 ... Unable to get the name property of the Series class".

Since I can see the Series names in the dialog box, I presumed that VBA can too, but apparently not. Here's the basic code I'm using:
[vba]Function GetTodayLineNo() As Long

Dim SeriesNo as Long, Temp as String

For SeriesNo = 1 To ActiveChart.SeriesCollection.Count
On Error GoTo Ignore
Temp = ActiveChart.SeriesCollection(SeriesNo).Name '<--Crash here
If Left(Temp, 5) = "Today" _
Then
GetTodayLineNo = SeriesNo
GoTo Finish
End If
Ignore:
Next SeriesNo
GetTodayLineNo = 0
Finish:

End Function 'GetTodayLineNo' [/vba]

An added annoyance is that the "On Error" statement is ineffective. Phffft! Any ideas??

[mvidas]

Hey CD,

Any chance of getting a sample sheet of what you're trying to do? I tried creating one with 9 series', created the chart, then cleared out one and it still worked (even when i commented out the on error statement).
I then tried deleting a column completely, the series name said #REF!, and that was what populated the Temp variable then.
I'm attaching mine in case I missed how you really set it up.

Matt

[Cyberdude]

Hey, Matt, thanx for taking the time to review this. Sorry for my delay in getting back to you.

I haven't had time to review your stuff, but I will tonight. I can't believe you didn't have any problems. I ran that sucker over and over again looking for the problem, and finally decided that I just couldn't do what I want to do. I guess I was hoping my (inelegant) code had a flaw. Pffft!

One difference (which shouldn't matter) is that my data isn't on the same sheet as the chart. Yours is. I'm using embedded charts, but they are all on a single sheet that I use for charts. Data is several sheets away. It will be difficult to send anything, but I'll try doing it in a different workbook and check to see if having data on a different sheet matters. With my luck it will work just fine in a different workbook. I'll get back to you.
Thanks again for your time.
Sid

[Cyberdude]

Matt, your chart is nothing like mine. I'm attaching a VERY abbreviated example of mine with the macros.

[Andy Pope]

Hi,

You will have trouble get information from a series that has blank data.
In your case it is not the 'Today Line' that is empty but the RSI series. Your error checking in the GetTodayLineNo was such that if an error was encountered it stopped looking. I have modified so it checks all series and returns the correct value when it finds the right label.
[vba]Function GetTodayLineNo() As Long '5/16/06
'Chart must be preselected
Dim SeriesNo&, Temp$
Dim strTemp As String
Application.ScreenUpdating = False
On Error Resume Next
For SeriesNo = 1 To ActiveChart.SeriesCollection.Count
strTemp = ""
strTemp = ActiveChart.SeriesCollection(SeriesNo).Name
If strTemp = "Today Line" _
Then
GetTodayLineNo = SeriesNo
GoTo Finish
End If
Next SeriesNo
GetTodayLineNo = 0
Finish:
Application.ScreenUpdating = True
End Function 'GetTodayLineNo'[/vba]