Creating Named Ranges in VBA
 

All,

Using Excel 2000, I'm unable to programmatically create a named range. I
have a series of lookup tables which I export from a database to an Excel
spreadsheet (all standard code/description-type lookup tables). I then want
to create a named range for each lookup table's data. I've tried both the
following methods and, while neither generates an error, they do not create
the named range:

1. MyWorksheet.Names.Add Name:="NewRangeName", RefersToR1C1:="R1C1:R14:C2"

2. Range(Cells(1, 1), Cells(14, 2)).Name = "NewRangeName"

Any ideas?

Thanks,

Mark D'Agosta

Creating Named Ranges in VBA
 

Put an equals within the RefersTo double-quotes, like this:

RefersToR1C1:="=R1C1:R14:C2"

Bill Barclift

"Mark D'Agosta" wrote in message
et...
All,

Using Excel 2000, I'm unable to programmatically create a named range. I
have a series of lookup tables which I export from a database to an Excel
spreadsheet (all standard code/description-type lookup tables). I then
want
to create a named range for each lookup table's data. I've tried both the
following methods and, while neither generates an error, they do not
create
the named range:

1. MyWorksheet.Names.Add Name:="NewRangeName",
RefersToR1C1:="R1C1:R14:C2"

2. Range(Cells(1, 1), Cells(14, 2)).Name = "NewRangeName"

Any ideas?

Thanks,

Mark D'Agosta

Creating Named Ranges in VBA
 

With Worksheets("Sheet1")
.Range(.Cells(1, 1), .Cells(14, 2)).Name = "NewRangeName"
End With
msgbox Range("NewRangeName").address(External:=True")

should work. It always does for me.

You don't have "on error resume next" set in your code do you?

--
Regards,
Tom Ogilvy

Mark D'Agosta wrote in message
et...
All,

Using Excel 2000, I'm unable to programmatically create a named range. I
have a series of lookup tables which I export from a database to an Excel
spreadsheet (all standard code/description-type lookup tables). I then
want
to create a named range for each lookup table's data. I've tried both the
following methods and, while neither generates an error, they do not
create
the named range:

1. MyWorksheet.Names.Add Name:="NewRangeName",
RefersToR1C1:="R1C1:R14:C2"

2. Range(Cells(1, 1), Cells(14, 2)).Name = "NewRangeName"

Any ideas?

Thanks,

Mark D'Agosta

Creating Named Ranges in VBA
 

Tom,

Yes, I did have "on error resume next" set! Earlier in the procedure I
wanted to clear a range that may or may not exist, so I turned off the error
handler... and forgot to turn it back on! Working much better now. Thanks.

Mark

"Tom Ogilvy" wrote in message
...
With Worksheets("Sheet1")
.Range(.Cells(1, 1), .Cells(14, 2)).Name = "NewRangeName"
End With
msgbox Range("NewRangeName").address(External:=True")

should work. It always does for me.

You don't have "on error resume next" set in your code do you?

--
Regards,
Tom Ogilvy

Mark D'Agosta wrote in message
et...
All,

Using Excel 2000, I'm unable to programmatically create a named range.
I
have a series of lookup tables which I export from a database to an
Excel
spreadsheet (all standard code/description-type lookup tables). I then
want
to create a named range for each lookup table's data. I've tried both
the
following methods and, while neither generates an error, they do not
create
the named range:

1. MyWorksheet.Names.Add Name:="NewRangeName",
RefersToR1C1:="R1C1:R14:C2"

2. Range(Cells(1, 1), Cells(14, 2)).Name = "NewRangeName"

Any ideas?

Thanks,

Mark D'Agosta

Creating Named Ranges in VBA
 

Bill,

Thanks for the tip. It did the trick. So now that I got two replies to
this post, I have two different methods I can use to programmatically create
my named range.

Thanks for your help.

Mark