Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Worksheet List to Named Array Constant
I have a list on a worksheet. I would like to convert that list to a named
range. Is there a VBA procedure that would do so? For example, in cells A1:A3 I have A1 - Jan A2 - Feb A3 - Mar I would like to convert this list to a defined name called "Months". The defined name dialog box "refers to" input for the name "Months" would look like: ={"Jan","Feb","Mar"} Thanks for your help. -- Steph |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Worksheet List to Named Array Constant
I think this does what you want...
Sub CreateNameArray() Dim X As Long Dim R As Range Dim N As String Set R = Worksheets("Sheet4").Range("A1:A3") N = "={" For X = 1 To R.Count N = N & """" & R(X).Value & """," Next N = Left(N, Len(N) - 1) & "}" Names.Add Name:="Months", RefersTo:=N End Sub -- Rick (MVP - Excel) "Steph" wrote in message ... I have a list on a worksheet. I would like to convert that list to a named range. Is there a VBA procedure that would do so? For example, in cells A1:A3 I have A1 - Jan A2 - Feb A3 - Mar I would like to convert this list to a defined name called "Months". The defined name dialog box "refers to" input for the name "Months" would look like: ={"Jan","Feb","Mar"} Thanks for your help. -- Steph |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Worksheet List to Named Array Constant
You actually don't need a VBA procedure to define a named range. You can
just select the range and define the name attached to it. If the range is varying in size over time, you can define a dynamic range. When you reference it, do something like this Dim myRange as Excel.Range Dim myWS as Excel.worksheet Set myWS = Worksheets("Sheet1") 'or whatever sheet it's on Set myRange = myws.Range("Months") "Steph" wrote: I have a list on a worksheet. I would like to convert that list to a named range. Is there a VBA procedure that would do so? For example, in cells A1:A3 I have A1 - Jan A2 - Feb A3 - Mar I would like to convert this list to a defined name called "Months". The defined name dialog box "refers to" input for the name "Months" would look like: ={"Jan","Feb","Mar"} Thanks for your help. -- Steph |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Worksheet List to Named Array Constant
???
range("a1:a12").name="Months" -- Don Guillett Microsoft MVP Excel SalesAid Software "Steph" wrote in message ... I have a list on a worksheet. I would like to convert that list to a named range. Is there a VBA procedure that would do so? For example, in cells A1:A3 I have A1 - Jan A2 - Feb A3 - Mar I would like to convert this list to a defined name called "Months". The defined name dialog box "refers to" input for the name "Months" would look like: ={"Jan","Feb","Mar"} Thanks for your help. -- Steph |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Worksheet List to Named Array Constant
Rick,
Thank you. That's exactly what I was looking for. -- Steph "Rick Rothstein" wrote: I think this does what you want... Sub CreateNameArray() Dim X As Long Dim R As Range Dim N As String Set R = Worksheets("Sheet4").Range("A1:A3") N = "={" For X = 1 To R.Count N = N & """" & R(X).Value & """," Next N = Left(N, Len(N) - 1) & "}" Names.Add Name:="Months", RefersTo:=N End Sub -- Rick (MVP - Excel) "Steph" wrote in message ... I have a list on a worksheet. I would like to convert that list to a named range. Is there a VBA procedure that would do so? For example, in cells A1:A3 I have A1 - Jan A2 - Feb A3 - Mar I would like to convert this list to a defined name called "Months". The defined name dialog box "refers to" input for the name "Months" would look like: ={"Jan","Feb","Mar"} Thanks for your help. -- Steph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
populate array with list of named range | Excel Programming | |||
Data validation list / named array constant | Excel Worksheet Functions | |||
Named Constant | Excel Programming | |||
Defined named range (Array list) | Excel Programming | |||
Search and replace using a named array on another worksheet | Excel Programming |