Microsoft Excel - Excel VBA - Name a range using RefersTo:=range(cells(),cells()) ??

Asked By Daniel Hatfield on 08-Aug-09 08:24 PM
Can anyone tell me why this works:
ActiveWorkbook.Names.Add Name:="Test", RefersTo:=Sheets(1).Range("c2:c10")

...but not this?
ActiveWorkbook.Names.Add Name:="Test", RefersTo:=Sheets(1).Range(Cells(2, 3), Cells(10, 3))

I want to use the second one so that I can throw in some variables to make a dynamic range - maybe there is a better way to do that (using VBA only)?

Thanks in advance,

Daniel

Both work for me - Rolf Jaeger replied to Daniel Hatfield on 09-Aug-09 02:28 AM

Hi Daniel:

both versions of this statement create a 'Refers To' of  =Sheet1!$C$2:$C$10 in my version of Excel (2003). Hence I unfortunately can't help you.

Best wishes,
Rolf



Bug? - Daniel Hatfield replied to Rolf Jaeger on 09-Aug-09 09:08 AM

Hi Rolf,

Thanks for checking this.  I am using 2007 and the second statement results in an error.  Maybe this is a bug in 2007?

Daniel

It works fine for me in Excel 2007 as well - Rolf Jaeger replied to Daniel Hatfield on 10-Aug-09 12:21 PM

Hi Daniel:

sorry to tell you, but I just checked this in my version of Excel 2007 and the second version works fine as well.

Best wishes,
Rolf

excel - mecong mecong replied to Daniel Hatfield on 02-Oct-09 05:06 AM
try ActiveWorkbook.Names.Add Name:="Test", RefersTo:=Sheets(1).Range(Sheets(1).Cells(2, 3), Sheets(1).Cells(10, 3))
Daniel replied to Daniel Hatfield on 18-Jan-11 06:53 PM
I'm having the same problem!