#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 Where:=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

0
Joe_Hunt
8/19/2008 5:48:05 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
363 Views

Similar Articles

[PageSpeed] 7

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 Where:=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
> 


0
jonxlmvpNO (4558)
8/19/2008 6:28:17 PM
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

0
Joe_Hunt
8/19/2008 6:41:10 PM
Reply:

Similar Artilces:

Transfer Files Using VBA
Hi, I'd like to transfer files from several drives on my pc to one cetnral location. From A:\Office1\ B:\Office2\ C:\Office3\ To D:\HeadQuarters\ My routine now is the following 1) Copy the files from drives: A, B, and C; and then 2) Paste the filesto the D drive 3) Delete the files permanently from drives A, B, and C. As you see this is a tedious routine, which I'd liket o avoid by using VBA. Can someone start me off with some code on how to accomplish this. Thanks so much in advance! Jrew Jrew, You could create a batch file (from the olden days) using XCOPY and desired bel...

Where free 2 sigma statistical process control chart, free?
where can I download a free statistical process control chart template? ...

VBA problems
Hello, I just started messing around with VBA and I got something approaching what I want (what I want is to highlight a row based on a value in the 1st column). This is highlighting the wrong row though! It looks like all the numbers are correct... for instance, when I step through it, I'll get the correct row selected but a different row highlighted! Please shed some light on this! It's quite frustrating. ' first column has dates from 12/2003 to 12/2006 Private Sub Worksheet_Activate() Const Yellow = 6 Dim row As String Range("A1").Select Do While...

Constant List
Is there an easy way to return the values of the ChartType constants? Excel 97 does not seem to list them in help and I have not been able to find them in google.group searches. Thanks M Select xlChartTypes in the Classes pane of the object browser. The constants are named in the members panel. If you select one, it provides the numeric equivalent at the bottom of the object browser, e.g., Const xlArea = 1 Member of Excel.XlChartType - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ M wrote: > Is there...

Excel --- Physical dimensions of a chart object
Is there a way to know the physical dimensions of a chart object on a worksheet? I have data in several worksheets and I would like all my charts to be of the same size (obviously this means that data with a smaller range of values will be more spread out than data with a larger range of values). Thanks, Nimmi This is just an example: Sub Macro2() ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select n = ActiveSheet.Shapes("Chart 1").Width m = ActiveSheet.Shapes("Chart 1").Height MsgBox (n & " BY " & m...

How do I add y-axis labeling on the right side of chart?
I'd like to add an axis with same values on the right side of the chart. You'll need to have at least two series in order to enter a secondary axis. On one of the series, change the Axis to Secondary axis. "DavidNHFCU" <DavidNHFCU@discussions.microsoft.com> wrote in message news:28759EE0-759E-4964-8F5E-6C49099653B6@microsoft.com... > I'd like to add an axis with same values on the right side of the chart. How can I force the y-axis at the bottom of my chart to appear for all 12 months when I only have Jan, Feb and March figures to put in the first thre...

What is the best chart for this....
When I want to compare how each salesperson's sales contributes to Total Sales, I can use a pie chart. But what do I use when I want to compare how each salesperson performed against the *average* of total sales? I am envisioning a vertical bar chart with a horizontal line at the average, but I need a solution that can be generated by a macro. Any advice? Thanks in advance. Patti Patti - That's how you would do it, a column chart with a horizontal line. On this web page, I give a couple techniques to achieve the horizontal line: http://peltiertech.com/Excel/Charts/AddLine...

Controlling Gap Width for BAR Chart
Hi, I have the following data for example. A = 2 B= 3 C = 4 I can create a 2 D Bar chart from this quite easily. Now If I make the data to A = 2 , 3 B= 3 , 4 C = 4 , 5 I do want to plot the data on the basis of first two values as mentioned in the first example, but I want to control the width of the Bar from the value given after comma. Please help. On Sat, 24 Feb 2007, in microsoft.public.excel.charting, Fazi <farazahmedtoor@gmail.com> said: >I have the following data for example. >A = 2 , 3 >B= 3 , 4 >C = 4 , 5 > >I want to plot the data on the basis of firs...

excel chart showing movable graph at what point we make a profit
in a business would like to make a chart based on accumulated Earnings & Profit/Loss for year to date, perhaps movable showing our break even point and by moving chart to a another earnings figure what profit we should make. For example put in earnings for YTD Profit/Loss YTD then I can move the earnings part of the chart to show what our profit would be for a given earnings figure. Alternatively If For example if we earn $200 for year so far and profit is $0 but any amount over that is 18% profit so I can move chart line to say $240 and it will say what profit I will make. Hope ...

"Print to Scale" Excel 2007 Charts
In Excel 2003, one could double-click a chart, print, and the chart would auto-size to fill a page. I am unable to print my newly-created Excel 2007 charts so they auto-size to print one-to-a-page (now requires several time-consuming steps for reformatting titles, axes, etc.). I do not have a "print to scale" option in my page setup. Printing to a Ricoh Aficio AP610N. Thanks so much. I will assume we are talking of an embedded chart (one sitting on a worksheet) and not a chart sheet. In Excel 2003 (all earlier) you did not double click, you just single clicked to...

Bar Chart (2D) won't let me 'gap' one bar behind the other?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a nice, functioning bar chart that has 2 data fields: ACTUAL and BUDGET. <br><br>Actual is on one axis and Budget is on the 2nd axis. <br><br>These charts look nice side by side but I want one bar to be partly behind the other. <br><br>So, I select a data set. <br> Choose Format Data Series <br> Options <br> Overlap... (and no matter what value I choose, I can't overlap)???? <br><br>But, if I can adjust the Gap Width. <br><...

Stacked and single column in same chart?
How can I do a chart with a stacked column beside a single column? When I build a stacked column chart, any new source data I add wants to put it in the same stacked column. Use one of the links on this page. You need to set up the data so the single column is in a stacking position with no other columns of data. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Einstine wrote: > How can I do a chart with a stacked column beside a sing...

Can anyone help with this chart?
I would like to have the following values/labels on the y and x axis of any style of chart: X-axis: July, August, September etc for a full financial year; Y-axis: FB, AP, DR, LA, PC. OR the other way around. Presently the Y-axis simply displays the labels 'Jan Jan Jan Jan Jan' etc. Thank you. Any help would be much appreciated. Check out Jon Peltier's article on creating a vertical category axis: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "A little stuck" wro...

center data labels on my column chart
How do i get my data labels to appear in the center of each column on my chart? Thanks. Click on one of the labels (that will select all the labels in the series) Choose Format>Selected Data Labels Select the Alignment tab From the Label Position dropdown, choose Center Immediately after formatting the first series, if there are other series in the chart -- Select a label in another series Press the F4 button on the keyboard, to repeat the formatting from the previous series Repeat for all series Dan wrote: > How do i get my data labels to appear in the center of > e...

Dynamic Chart #2
Can you please help me to create a dynamic chart by using VBA? Thanks in advance!! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements VBA may not be needed. See the Excel | Tutorials | 'Dynamic Charts' page of my web site. -- [Posted directly to the Usenet newsgroup -- no affiliation with Excelforum] Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel...

2007 charting problems
Is anybody having these problems, and can anybody tell me how to fix them? 1. In Excel 2003, I could select a range of cells and drag them into a chart and they would be pasted as a new series into the chart. Is this feature gone in 2007? 2. When I have a rotated Y-axis label, I can click on the text and see an insert cursor, but when I start typing, only the first letter appears in the label and the rest end up in the formula bar. When I hit enter, only the new text from the formula bar shows up in the label. 1) Just Copy the range, right click the chart and use Paste 2) Do not use ...

chart zero
Hello, first Post - so here goes.... I am ploting a simple chart in Excel - when the value of a cell, say F5, is zero (calculated as =F4/F3) or F4 and F3 are zero and F5 returns #DIV/0! - I don't want to plot that cell at all. At the moment it is plotted as zero in both cases. I'm using Excel 2000. Many Thanks Andy Have just found the other threads dealing with this issue - #N/A and a white font will do it for me. Thanks anyway guys Andy "First & Last" wrote: > Hello, first Post - so here goes.... > > I am ploting a simple chart in Excel - when the va...

Charting Problems #2
I need to create a chart that has axes in "exact" inches. For example, the printed plot area is 6" x 3", or at least 1 axis is exactly inches. ...

Separating parts of a string in VBA
Hello, I have a string in cells(1,1). The value of the cell is "DATE" 1/1/10 THRU 3/1/10" I want to separate this string into the two new strings such that one string would read "1/10/10" and the other would read "3/1/10". In C programming, this would be fairly easy. But in VBA I don't know any string functions which can read and compare individual characters. Does anyone know how to accomplish what I am trying to do? thanks IF the DATE and THRU parts of your example text (along with their adjacent blank spaces) appear in all the text s...

adding a line to an existing chart
Hi, I have a chart with bars and two lines but I want to add another line on top of what I already have...can I do this if so, how? I have tried many things but it is not showing up. Please provide as much detail as possible, as I am fairly new to this. JudyT Select the data for the new line (say F2:F20) Click the Copy tool Click the chart Use menu command Edit | Past Special; specify New Series; click OK Suppose the data is G2:F20 with new x-values in column F Select the data for the new line (say G2:F20) Click the Copy tool Click the chart Use menu command Edit | Past Special; speci...

Plus / Minus Charts?
Does anyone know if it is possible to create a chart showing a positive and negative assessment of a feature. I don't know exactly what the chart is called and I can't attach a mockup images, so what I am looking for is: Feature 1: #######0&&&&&& Feature 2: ####0&&& Feature 3: #####0&&&&&& Axis 9876543210123456789 -ve +ve Any pointers gratefully received. Thanks Neil Hi Neil, put - labels in A2 and down - negative numbers in B2 and down - positiv...

Candlestick Charts
Sorry if this a repeat from another post, but I am new to the newsgroup. I am looking for an excel spreadsheet for Japanese Candlestick Charting. Any help is appreciated. Rich in Maine Rich - Make a line chart with your two series of data. Double click on of the series, choose the Options tab, and check the box for Up-Down Bars. Where the first series' value is higher than the second's, the bars are one color, when they are switched, the color changes. You can format the bars as if they were columns in a regular column chart. If you mean a stock candlestick chart, which show...

stacked column chart not baselining at 0
I have a data set with categories in columns A-B, and data in columns C-I. I've configured my chart to use stacked bars for the first 6 data series and a line for the last one. The first 6 are revenue allocations, with the 7th being a cumulative total. Most of the chart looks as expected, but the first category or row of data is displaying oddly. The figures in this row are all negative (or 0), so I expect the top of the bar to sit at zero, but it doesn't. The top of the bar sits at the value of the 7th/line series, so that the bottom of the bar is at 2x that (negative) value. ...

How to create 2 charts on one page
I have created a yearly chart for widgets accomplished in Excel (has 4 data lines). I would like to add another chart directly below this to show the turn-time for accomplishing the widgets. It would have the same X-axis values (months of the year). I tried using a secondary Y axis, and the chart is just unreadable with this approach. Can this be done. HELP. You haven't told us why it's unreadable, so I doubt that we can help. If your problem is that the two sets of data are overlapping, and you said you wanted the second chart *below* the first one, try adjusting the scales on...

vba excel project
im not sure if this is possible in excel... but here's what my boss wants... one excel file (master file) contains 10 command buttons (sheet 1). when a user clicks on a button (button 1), an excel template file (template 1) is automatically opened... now this template file will be filled up by the user, and once she/he saves the template (template 1) with a new filename, the date and time (of saving) will be listed on the master file (sheet 2) together with the specified filename of the user. now if the user clicks on button 2, template 2 is opened. and then once saved, the date,time a...