Named Range
 

I've named few ranges through VBA by using this code

For i = 1 To 15

' Define the name for the variables
ActiveWorkbook.Names.Add Name:=Worksheets("ST&GT DATA").Cells(1, i),
RefersToR1C1:= _
"=OFFSET('ST&GT DATA'!R1C1,'ST&GT DATA'!R1C214," & (i - 1) &
",'ST&GT DATA'!R1C216,1)"
Next i

which is working fine, but when i try to refer it in a chart, an error
msg is popping up saying "Your formula contains an invalid external
reference to a worksheet, Verify that the path, workbook, and range
name or cell reference are correct, and try again"

Can any one solve this problem?

Thanks

Gautam VK

Named Range
 

The code works fine except for the following two issues

1) You can't start the FOR loop at 1. You must start at two because i - 1
will equal zero, or change i - 1 to just i.
2) You have to have 'ST&GT DA!A1:A1:O1 unique strings to use as the named
ranges. the last column my chage to 16 for item 1 problem, then the last
column must change from 'O' to 'P'.

"Gautam" wrote:

I've named few ranges through VBA by using this code

For i = 1 To 15

' Define the name for the variables
ActiveWorkbook.Names.Add Name:=Worksheets("ST&GT DATA").Cells(1, i),
RefersToR1C1:= _
"=OFFSET('ST&GT DATA'!R1C1,'ST&GT DATA'!R1C214," & (i - 1) &
",'ST&GT DATA'!R1C216,1)"
Next i

which is working fine, but when i try to refer it in a chart, an error
msg is popping up saying "Your formula contains an invalid external
reference to a worksheet, Verify that the path, workbook, and range
name or cell reference are correct, and try again"

Can any one solve this problem?

Thanks

Gautam VK