Problem with SeriesCollection

[aryan_hr]

Dear Mr Excel,

I have a SeriesCollection in my chart. I can access its points by:
Dim TSer
For Each TSer In Sheets("Chart1").SeriesCollection
Debug.Print TSer.Points.Count
Next
and Sheets("Chart1").SeriesCollection.count is 1.

But if I delete a data (not graph) from Sheets("Chart1"), the Sheets("Chart1").SeriesCollection.count is still 1!!!
and I have a run-time error 1004 in my code and I cann't access Series properties such as Point, Name and........
Any idea to solve the error?
How can I understand the Series has data or not?


Best Regads,

 

[aryan_hr]

any help?????
No help????? and Where is the Mr.Excel??????????

 

[Andrew Poulsom]

If you right click the Series, what is the formula in the Formula Bar?

 

[aryan_hr]

You can not select it by right clicking!!!!! there is no graph in the chartsheet!!! but Sheets("Chart1").SeriesCollection.count is 1.

try this, draw a chart and then delete the data from sheets. The chartsheet is empty but Sheets("Chart1").SeriesCollection.count is 1


any help?

 

[Andrew Poulsom]

How are you deleting the data?

If you just delete the cell contents the Series still has the same number of points as previously. If you delete the rows/columns containing the Series data, you get an invalid reference error when you activate the Chart Sheet. But there is still a Series with 1 point.

 

[aryan_hr]

I JUST delete the cell contents ... and the Series don't have the same number of points as previously because the following line showes an error!!!!
Sheets("Chart1").SeriesCollection(1).Points.Count

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Pts As Points
    On Error Resume Next
    Set Pts = Sheets("Chart1").SeriesCollection(1).Points
    If Err <> 0 Then
       MsgBox "No points"
    Else
       MsgBox Pts.Count & " points"
    End If
    On Error GoTo 0
End Sub

 

[Jon Peltier]

When a series has no data, it is not plotted, and VBA cannot access the series. You can still manually get to it through the Source Data dialog, but VBA is a little stupid on this point. SeriesCollection.Count will include the series, but since it isn't plotted, SeriesCollection.Points.Count throws an error.

I haven't tried this yet myself, but a colleague of mine, Bill Manville, has suggested changing the chart to an area chart. Apparently VBA can access a data-less area chart series. When you have finished, change the chart back. The only difficulty is when you have a combo chart, and you have to revert each series to a different type. Iterate through the series and store the chart type; since our misbehaving series has no chart type (because it isn't plotted), you need to trap that error.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

 

[aryan_hr]

Thanks my friends for the replys, I try the code...

Best Regards,
Hamid


P.S.: Dear jonpeltier, thanks for the good reply and your site....