Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem creating named ranges in a Macro! | Excel Discussion (Misc queries) | |||
Creating dynamic ranges which are named. | Excel Worksheet Functions | |||
Using named 3-D ranges | Excel Discussion (Misc queries) | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Named Ranges | Excel Worksheet Functions |