Series Collection Name 1

[Strat121]

Having issues getting the program to recognize the series collection name for a graph:

Count = 1
For Each wSheet In Worksheets
If wSheet.Name <> ActiveSheet.Name Then
Count = Count + 1
ElseIf wSheet.Name = ActiveSheet.Name Then
Exit For
End If
Next wSheet

Sheets("Graph").Select
ActiveChart.SeriesCollection(Count).Name = Worksheets(Count).Name

It works perfectly the first time around. But if I delete the active sheet, then insert a new worksheet with the same name and same data, it gives me the run-time 1004 "Unable to set Name property of Series class" error.

 

[SkipVought]

Hi,

Don't need the loop...

iCount = Worksheets.Count - 1
Sheets("Graph").SeriesCollection(iCount).Name = Worksheets(iCount).Name

BUT the question is, do you have at least the same number of SERIES in your chart as you have WORKSHEETS in your workbook?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Strat121]

Yeah, plenty of series in the graph. 27, actually. And it has no problem working the first time around, the name changes appropriately. But if sheets get deleted and replaced in the same location, it won't recognize the proper series collection location. It will work at places like (Count - 1) or (Count + 1), but not at (Count)

 

[SkipVought]

Use the Watch Window to determine which object is throwing the error.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Strat121]

I did. For example. Run it the first time, Count = 5 so the 5th series name will change. Delete the sheet, add a sheet in the same location. Change the name to the same name. Run the program again. Count = 5 but it errors. I type in SeriesCollection(5), still errors. Tried 4 and 6, both worked. But 5 errors.

 

[SkipVought]

What is the RANGE REFERENCE of that series? Was that SERIES RANGE REFERENCE pointing to the sheet that you deleted?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Strat121]

ActiveChart.SeriesCollection(Count).Name = Name
ActiveChart.SeriesCollection(Count).XValues = "='" & Name & "'!R6C4:R" & Total + 5 & "C4"
ActiveChart.SeriesCollection(Count).Values = "='" & Name & "'!R6C3:R" & Total + 5 & "C3"

It always errors on the .Name line. If I change just the SeriesCollection of the .Name, as per previous example, to 4 or 6, the other lines read normally with Count = 5

 

[SkipVought]

Hummmmm?

Series X has a reference pointing to Sheet X.

Sheet X is DELETED.

Guess what happens?

If you look in the Chart > Source Data - Series TAB. and select the series in question JUST AFTER THE SHEET IS DELETED, the issue will be graphically apparent.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Strat121]

But the series still remains, and should still be able to be selected and modified using the Series Collection. The code redefines the Series parameters every time the program is run. Even if the sheet is deleted, and the name becomes #REF in the Source Data, is it not still counted as a Series and numbered according to it's location in the Source Data?

 

[SkipVought]

This is a typical novice problem that comes with deleting, CELLS, ROWS or COLUMNS or SHEETS, where formulas reference those MISSING OBJECTS.

NO, the Excel program can't read you mind, that you want to REPAIR a mistake of deleting an OBJECT.

The DELETE occurs.

The REFERENCE is DESTROYED!

You come back immediately or 10 years later and restore the deleted object.

Excel knows from NOTHING, what that reference was. YOU DESTROYED IT!

You could rerun your original code, I suppose, but WHY would you be deleting referenced objects in a workbook???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Strat121]

All right, obviously I'm missing something. I understand that the source data can't read from a worksheet that doesn't exist. And when a worksheet is created, even with the same name, that the chart doesn't automatically update. But what I don't understand is why, if the Series STILL exists in the graph, the SeriesCollection is unable to recognize it even though it is still in the same location of the chart's source data. Yes, the source data references incorrectly, but it's still there. Why won't the Series Collection allow me to select the Nth series and modify it, regardless of what it's trying to reference?

 

[SkipVought]

The KEY is the sequence of assigning values...

    Application.DisplayAlerts = False
    With Sheet2.ChartObjects(1).Chart
        .SeriesCollection(iCount).XValues = Sheets(sName).Range([A2], [A2].End(xlDown))
        .SeriesCollection(iCount).Values = Sheets(sName).Range([B2], [B2].End(xlDown))
        .SeriesCollection(iCount).Name = sName
    End With
    Application.DisplayAlerts = True

Also avoid using the Seletion or ActiveAnything objects. Reference objects explicitly whenever possible.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Strat121]

Excellent. Thank you! It was the order that was causing the error.

 

[SkipVought]

But WHY are you deleting a worksheet?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Strat121]

It's a template that allows the user to add sheets that are properly formatted. Every time the program is run on one of those sheets, it analyzes the data and throws certain parts into various graphs. File size was apparently an issue, so I had to delete all the preset source ranges and program it to just graph the necessary data. I was told it's supposed to be "idiot-proof". So one idiot ended up deleting a sheet and remaking it and said it wouldn't graph correctly and that it kept causing an error. I was unaware that if the X- or Y-Values had a reference error that the name couldn't be changed before the reference was fixed.