Runtime Error 1004 - setting name as a source of chart

  • Thread starter Thread starter anupam
  • Start date Start date
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
 
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
Click to expand...

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/
_______
 
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
 
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
 
Back
Top