Dear experts,
I would like to insert a chart in a spreadsheet
automatically with VBA. The chart refers to 4 ranges in
rows, each range is a different data serie.
Sometimes it might be that a whole serie has no data in
it - so I get an error from VBA. I have bypassed it by
stating "On error resume next", but it has the
disadvantage that instead of getting in the legend the
name of serie where there are no data, I only get "serie
3" or "serie 4"... quite awful in a chart!
Is there a way I can get the name of the serie even if it
contains no data?
Many thanks in advance!
Best regards,
Valeria

In case it might help, here is my code:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range
("A20"), PlotBy:= _
xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = X_Data
ActiveChart.SeriesCollection(1).Values = Tot_Cost
ActiveChart.SeriesCollection(1).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 1, 4)
ActiveChart.SeriesCollection(2).Values = Int_Cost
ActiveChart.SeriesCollection(2).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 2, 4)
ActiveChart.SeriesCollection(3).Values = Ext_Cost
On Error Resume Next
ActiveChart.SeriesCollection(3).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 3, 4)
ActiveChart.SeriesCollection(4).Values = Price_Data
On Error Resume Next
ActiveChart.SeriesCollection(4).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 7, 4)

You have entered the On Error Resume Next line after the
lines where you are setting the values. If the error is
occurring in the line where the values are being set then
the On Error Resume Next should be before those lines.

Try this:

On Error Resume Next
ActiveChart.SeriesCollection(3).Values = Ext_Cost
ActiveChart.SeriesCollection(3).Name = Workbooks _
(Product).Worksheets(1).Cells(ProductLastRow + 3, 4)
On Error Resume Next
ActiveChart.SeriesCollection(4).Values = Price_Data
ActiveChart.SeriesCollection(4).Name = Workbooks _
(Product).Worksheets(1).Cells(ProductLastRow + 7, 4)

When I did a little testing on this using a chart and data
from a macro of my own I did not get an error when there
was no data in a row that I had assigned as chart values.
But all my data was on the same sheet. This makes me think
that possibly you are pulling your data from different
sheets and the error occurs when the sheet has not been
automatically created or something. This would pose a
problem for trying to get a name - if the sheet that is
supposed to hold it is not there.

Assuming that missing sheets(or something like that) is
what you are facing. Just have your macro make a "scratch"
sheet that contains the titles for all the series and put
an If statement after each line that assigns the names
like this:

On Error Resume Next
ActiveChart.SeriesCollection(3).Values = Ext_Cost
ActiveChart.SeriesCollection(3).Name = Workbooks _
(Product).Worksheets(1).Cells(ProductLastRow + 3, 4)
If ActiveChart.SeriesCollection(3).Name = "Series 3" Then
ActiveChart.SeriesCollection(3).Name = Worksheets _
(ScratchSheet).Range(SeriesThreeName)
End If

'Add this somewhere after all the names have been assigned
Application.DisplayAlerts = False
Sheets(ScratchSheet).Delete
Application.DisplayAlerts = True


1) set "ScratchSheet" to the name of the page where you
put the names
2) set "SeriesThreeName" to the cell range on the scratch
sheet that has the name for series three

Have fun!

-IA


-----Original Message-----
Dear experts,
I would like to insert a chart in a spreadsheet
automatically with VBA. The chart refers to 4 ranges in
rows, each range is a different data serie.
Sometimes it might be that a whole serie has no data in
it - so I get an error from VBA. I have bypassed it by
stating "On error resume next", but it has the
disadvantage that instead of getting in the legend the
name of serie where there are no data, I only get "serie
3" or "serie 4"... quite awful in a chart!
Is there a way I can get the name of the serie even if it
contains no data?
Many thanks in advance!
Best regards,
Valeria

In case it might help, here is my code:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range
("A20"), PlotBy:= _
xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = X_Data
ActiveChart.SeriesCollection(1).Values = Tot_Cost
ActiveChart.SeriesCollection(1).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 1, 4)
ActiveChart.SeriesCollection(2).Values = Int_Cost
ActiveChart.SeriesCollection(2).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 2, 4)
ActiveChart.SeriesCollection(3).Values = Ext_Cost
On Error Resume Next
ActiveChart.SeriesCollection(3).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 3, 4)
ActiveChart.SeriesCollection(4).Values = Price_Data
On Error Resume Next
ActiveChart.SeriesCollection(4).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 7, 4)
.

You could do something like the untested
If IsEmpty (Workbooks(Product).Worksheets(1) _
.Cells(ProductLastRow + 3, 4)) Then
ActiveChart.SeriesCollection(3).Name = "No data" _
Else
ActiveChart.SeriesCollection(3).Name = _
Workbooks(Product).Worksheets(1) _
.Cells(ProductLastRow + 3, 4)
End If
--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Dear experts,
I would like to insert a chart in a spreadsheet
automatically with VBA. The chart refers to 4 ranges in
rows, each range is a different data serie.
Sometimes it might be that a whole serie has no data in
it - so I get an error from VBA. I have bypassed it by
stating "On error resume next", but it has the
disadvantage that instead of getting in the legend the
name of serie where there are no data, I only get "serie
3" or "serie 4"... quite awful in a chart!
Is there a way I can get the name of the serie even if it
contains no data?
Many thanks in advance!
Best regards,
Valeria

In case it might help, here is my code:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range
("A20"), PlotBy:= _
xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = X_Data
ActiveChart.SeriesCollection(1).Values = Tot_Cost
ActiveChart.SeriesCollection(1).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 1, 4)
ActiveChart.SeriesCollection(2).Values = Int_Cost
ActiveChart.SeriesCollection(2).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 2, 4)
ActiveChart.SeriesCollection(3).Values = Ext_Cost
On Error Resume Next
ActiveChart.SeriesCollection(3).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 3, 4)
ActiveChart.SeriesCollection(4).Values = Price_Data
On Error Resume Next
ActiveChart.SeriesCollection(4).Name = Workbooks
(Product).Worksheets(1).Cells(ProductLastRow + 7, 4)