#N/A isn't charting in VBA
 

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
 

You might get a better response from the Charts news group.

"Joe_Hunt" wrote:

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
 

I didn't know there was a charts group. I'll go there. Thank you.

JLGWhiz wrote:
You might get a better response from the Charts news group.

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
[quoted text clipped - 45 lines]
Application.ScreenUpdating = True
End Sub