Microsoft KB Archive/132968

= Recorded Macro Fails to Insert New Data to Chart =

Article ID: 132968

Article Last Modified on 10/10/2006

-

APPLIES TO


 * Microsoft Excel 95a
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q132968



SYMPTOMS
In the versions of Microsoft Excel listed above, if you record a macro to insert a non-contiguous, or non-adjacent, range of cells into a chart, you will receive the following error message when you run the macro:

Run-time error '1004'

Error in formula.



CAUSE
The error occurs because the complete reference, which includes the workbook and worksheet name, is recorded only for the first range of cells selected. The additional ranges specified in the macro do not contain the workbook and worksheet names necessary to make the macro work properly. For example, if you select cell A1, then hold down the CTRL key and select cells C1 and D1, the references are recorded as:

[Book2]Sheet1!R3C1,R3C2,R3C3

This problem occurs when you insert new data to a chart sheet or to an embedded chart on a worksheet other than the worksheet that contains the chart data.



RESOLUTION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To avoid receiving a macro error when you run the recorded macro, modify it by adding the workbook and worksheet name to the cell references that are incomplete. For example, the following macro is recorded when you insert new data to an existing chart sheet: Sub Macro1 ActiveChart.SeriesCollection.Add Source:= _ "[Book1]Sheet1!R2C1,R2C3,R2C5", Rowcol:=xlRows, _ SeriesLabels:=False, CategoryLabels:=False, Replace:=False End Sub For this macro to work properly, change the Source argument string as shown below in the following example: Sub Example1 ActiveChart.SeriesCollection.Add Source:= _ "[Book1]Sheet1!R2C1,[Book1]Sheet1!R2C3,[Book1]Sheet1!R2C5", _ Rowcol:=xlRows, SeriesLabels:=False, CategoryLabels:=False, _ Replace:=False End Sub Another way to modify the Source argument is to use a Range reference to refer to the range of cells you are inserting: Sub Example2 ActiveChart.SeriesCollection.Add Source:= _ Workbooks("Book1").Worksheets("Sheet1").Range("A2,C2,E2"), _ Rowcol:=xlRows, SeriesLabels:=False, CategoryLabels:=False, _ Replace:=False End Sub NOTE: In both of the examples shown above, the references to the workbook name are not necessary if the chart exists in the same workbook as the source data.



STATUS
Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed at the beginning of this article. This problem was corrected in Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition.

Additional query words: 7.00a 5.00a 5.00c XL5 XL7 XL

Keywords: kbbug kbcode kbfix kbprogramming KB132968

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.