Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
#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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
#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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
#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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charting help... | Charts and Charting in Excel | |||
Charting help! | Charts and Charting in Excel | |||
Charting | Excel Worksheet Functions | |||
Boy do I need your help with charting! | Charts and Charting in Excel | |||
Custom charting - Stacked charting with a line | Charts and Charting in Excel |