#N/A isn't charting in VBA
 

I was told in the programming group to come here. I have a problem I'm hoping
someone can give me a hand with. I've been asked to create the ability to
make charts on any page of a workbook by clicking a button on the worksheet.
I've been able to do that, except that there is data from three non-
contiguous rows on the Y axis, and if there are any #N/A's it's erroring out.
It doesn't error out when I do it manually though. Can anyone give me a clue?
Here's the code I have in there now. There are 30+
sheets by the way. I appreciate any help.

Sub ChartCreation()
'
' ChartCreation Macro
' Macro recorded 8/15/2008 by jhunt

Application.ScreenUpdating = False
Dim shtData As Worksheet

Set shtData = ActiveSheet

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=shtData.Range("E2")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "R11C3:R46C3"
ActiveChart.SeriesCollection(1).Values = "='" & shtData.Name & "'!R11C5:
R46C5"
ActiveChart.SeriesCollection(1).Name = "=""ALG"""
ActiveChart.SeriesCollection(2).Values = "='" & shtData.Name & "'!R11C20:
R46C20"
ActiveChart.SeriesCollection(2).Name = "=""Pros"""
ActiveChart.SeriesCollection(3).Values = "='" & shtData.Name & "'!R11C36:
R46C36"
ActiveChart.SeriesCollection(3).Name = "=""Recommended"""
ActiveChart.Location Whe=xlLocationAsObject, Name:=shtData.Name
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Range("B5")
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month
Index"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Values"
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.ChartArea.Select
Application.ScreenUpdating = True
End Sub

#N/A isn't charting in VBA
 

Is it when you have "any" #N/A values, or when a series' entire range
contains #N/A? I presume it's the latter. VBA chokes on XY and Line charts
when an entire series is unplottable due to blanks of #N/A. The standard
answer is to make a column chart, then change it to a line chart after
you've done all the other manipulation of the chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"Joe_Hunt" <u45578@uwe wrote in message news:88e9deeb423e2@uwe...
I was told in the programming group to come here. I have a problem I'm
hoping
someone can give me a hand with. I've been asked to create the ability to
make charts on any page of a workbook by clicking a button on the
worksheet.
I've been able to do that, except that there is data from three non-
contiguous rows on the Y axis, and if there are any #N/A's it's erroring
out.
It doesn't error out when I do it manually though. Can anyone give me a
clue?
Here's the code I have in there now. There are 30+
sheets by the way. I appreciate any help.

Sub ChartCreation()
'
' ChartCreation Macro
' Macro recorded 8/15/2008 by jhunt

Application.ScreenUpdating = False
Dim shtData As Worksheet

Set shtData = ActiveSheet

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=shtData.Range("E2")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "R11C3:R46C3"
ActiveChart.SeriesCollection(1).Values = "='" & shtData.Name & "'!R11C5:
R46C5"
ActiveChart.SeriesCollection(1).Name = "=""ALG"""
ActiveChart.SeriesCollection(2).Values = "='" & shtData.Name &
"'!R11C20:
R46C20"
ActiveChart.SeriesCollection(2).Name = "=""Pros"""
ActiveChart.SeriesCollection(3).Values = "='" & shtData.Name &
"'!R11C36:
R46C36"
ActiveChart.SeriesCollection(3).Name = "=""Recommended"""
ActiveChart.Location Whe=xlLocationAsObject, Name:=shtData.Name
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Range("B5")
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month
Index"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Values"
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.ChartArea.Select
Application.ScreenUpdating = True
End Sub

#N/A isn't charting in VBA
 

That did the trick. Thank you very much!

Jon Peltier wrote:
Is it when you have "any" #N/A values, or when a series' entire range
contains #N/A? I presume it's the latter. VBA chokes on XY and Line charts
when an entire series is unplottable due to blanks of #N/A. The standard
answer is to make a column chart, then change it to a line chart after
you've done all the other manipulation of the chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

I was told in the programming group to come here. I have a problem I'm
hoping
[quoted text clipped - 53 lines]
Application.ScreenUpdating = True
End Sub