Runtime Error 1004 - setting name as a source of chart

A

anupam

Dim TrendChart As Variant, sourcedata As Variant, i As Integer, n A
Integer, j As Integer

ActiveWorkbook.Names.Add Name:="CSource_1"
RefersToR1C1:="'1'!R1C26:INDEX('1'!C27,COUNTA('1'!C26))"
ActiveWorkbook.Names.Add Name:="CSource_2"
RefersToR1C1:="'2'!R1C26:INDEX('2'!C27,COUNTA('2'!C26))"

sourcedata = Array("CSource_1", "CSource_2", "DSource_2", "CSource_3"
"DSource_3", "CSource_4", .................... "CSource_34"
"DSource_34", _
"CSource_35", "DSource_35")
j = 0
'i = 1
For n = 3 To 5 'Worksheets.Count

Worksheets(n).Select
For i = 0 To Worksheets(n).ChartObjects.Count
Worksheets(n).Select
Worksheets(n).ChartObjects(i + 1).Activate

ActiveChart.SetSourceDat
Source:=Sheets(n).Range(sourcedata(i)), PlotBy:=xlColumns(here i a
getting the error)
i = i + 1
Next i
Next n

Getting error 1004
 
J

Jon Peltier

Your names refer to ranges on different sheets. "CSource_1" is on sheet
named "1" (not on sheet1), and "CSource_2" is on sheet "2". If sheets(1)
is not sheets("1"), then range("CSource_1") is undefined.

BTW, this whole block can be simplified, since you don't need to select
or activate things before VBA works on them:
Worksheets(n).Select
For i = 0 To Worksheets(n).ChartObjects.Count
Worksheets(n).Select
Worksheets(n).ChartObjects(i + 1).Activate
ActiveChart.SetSourceData

For i = 0 To Worksheets(n).ChartObjects.Count
Worksheets(n).ChartObjects(i + 1).SetSourceData

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
A

anupam

Hi! Jon Peltier ,

As u can see that my loop start with n=3 i.e. sheet index is 3 and th
sheet name is '1'. I have not started with sheet index 1.
Then also i am facing this error.

waiting for a quick respons
 
T

Tushar Mehta

Check the names you are creating. Array enter =CSource_1 in a range of
cells. What do you get?

Now, turn on the macro recorder and create the named formulas of
interest. Turn off the recorder. What do you get? Notice the
difference between the code you posted and the code that XL generated?

I still don't know if you can use the result in a chart (other issues
might still be a factor), but the above steps will resolve one problem.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2004
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top