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.