.referstorange with Offset problem

[hvalcourt]

Hi everyone,


I have a few names whose reference formula uses offset like :

colOf100Cells = offset($a$1 , 0 ,0 , 100 , 1 ).

Whenever I try to do something like :

dim somevar as range 
set somevar = wbk.names("colOf100Cells").referstorange

I get an a runtime error 1004.

This so far happens only when the definition of the name includes offset.
I don't understand why since it's very clear what colof100cells refers or points to. If I use colOf100Cells in the worksheet, it works whenever a range is required.

Anyone knows of a workaround for this ?

Thanks,

 

[hvalcourt]

Here are a few examples from my immediate window.
Why can't i access the actual range ??

? activeworkbook.names(5).refersto
=OFFSET( Sheet1!$J$1, 1, 0, COUNTA(Sheet1!$A:$A) - 1, 1)
? activeworkbook.names(5).name
Sheet1!rev
? activeworkbook.names(5).referstorange.Rows.Count
ERROR
?activesheet.name
Sheet1
?activesheet.names(5).name
Sheet1!rev
?activesheet.names(5).refersto
=OFFSET( Sheet1!$J$1, 1, 0, COUNTA(Sheet1!$A:$A) - 1, 1)
?activesheet.names(5).referstorange.rows.count
ERROR