Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range to Array Oddity
I've just used a named range to fill an array and have found a
peculiarity that I'm not sure about. The named range was not contiguous and when assigning it to the array the array only appears to have used the second section of the range. Is this what normally happens and is there a work around? If I gave each part of the original range a separate name could I then read them easily in to the same array? How? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range to Array Oddity
Hi
could you provide the exact name definition and your assignment code. Just as a guess: You have to use the areas collection to loop through all areas of your range "bloycee" wrote: I've just used a named range to fill an array and have found a peculiarity that I'm not sure about. The named range was not contiguous and when assigning it to the array the array only appears to have used the second section of the range. Is this what normally happens and is there a work around? If I gave each part of the original range a separate name could I then read them easily in to the same array? How? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range to Array Oddity
I created the named range through the worksheet rather than VB so have
something like "MyNamedRange" is =Sheet1!$H$2:$K$13,Sheet1!$A$2:$D$21 from the Insert, Name, Define menu controls. The assignment I tried was simply: Dim MyArray as Variant MyArray=Range("MyNamedRange").Value This returned the data from cells H2 to K13 but not that in A2 to D21 Frank Kabel wrote: Hi could you provide the exact name definition and your assignment code. Just as a guess: You have to use the areas collection to loop through all areas of your range "bloycee" wrote: I've just used a named range to fill an array and have found a peculiarity that I'm not sure about. The named range was not contiguous and when assigning it to the array the array only appears to have used the second section of the range. Is this what normally happens and is there a work around? If I gave each part of the original range a separate name could I then read them easily in to the same array? How? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range to Array Oddity
You can't assign a discontiguous range to an array directly
(using a command like varr = Range("MyArray").Value) You generally can't append values to an array in bulk (as above). you can loop through all the cells in the range and add them one at a time to the array. -- Regards, Tom Ogilvy "bloycee" wrote in message oups.com... I've just used a named range to fill an array and have found a peculiarity that I'm not sure about. The named range was not contiguous and when assigning it to the array the array only appears to have used the second section of the range. Is this what normally happens and is there a work around? If I gave each part of the original range a separate name could I then read them easily in to the same array? How? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range to Array Oddity
Hi
then see Tom's reply "bloycee" wrote: I created the named range through the worksheet rather than VB so have something like "MyNamedRange" is =Sheet1!$H$2:$K$13,Sheet1!$A$2:$D$21 from the Insert, Name, Define menu controls. The assignment I tried was simply: Dim MyArray as Variant MyArray=Range("MyNamedRange").Value This returned the data from cells H2 to K13 but not that in A2 to D21 Frank Kabel wrote: Hi could you provide the exact name definition and your assignment code. Just as a guess: You have to use the areas collection to loop through all areas of your range "bloycee" wrote: I've just used a named range to fill an array and have found a peculiarity that I'm not sure about. The named range was not contiguous and when assigning it to the array the array only appears to have used the second section of the range. Is this what normally happens and is there a work around? If I gave each part of the original range a separate name could I then read them easily in to the same array? How? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range to Array Oddity
This seemed to work for me in very limited testing
Sub test() Dim v, i as Long, aCnt as long 'ActiveWorkbook.Names.Add Name:="myname", _ 'RefersTo:=Range("Sheet1!$H$2:$K$13,Sheet1!$A$2:$D $21") ' 'For Each cell In Range("myname") 'i = i + 1 'cell.Value = i 'Next With Range("myname") aCnt = .Areas.Count ReDim v(1 To aCnt) As Variant For i = 1 To aCnt v(i) = .Areas(i).Value Next End With 'test it Dim rw As Long, cl As Long For i = 1 To UBound(v) Debug.Print "Area " & i For rw = 1 To UBound(v(i), 1) For cl = 1 To UBound(v(i), 2) Debug.Print v(i)(rw, cl) Next Next Next End Sub Regards, Peter "bloycee" wrote in message oups.com... I created the named range through the worksheet rather than VB so have something like "MyNamedRange" is =Sheet1!$H$2:$K$13,Sheet1!$A$2:$D$21 from the Insert, Name, Define menu controls. The assignment I tried was simply: Dim MyArray as Variant MyArray=Range("MyNamedRange").Value This returned the data from cells H2 to K13 but not that in A2 to D21 Frank Kabel wrote: Hi could you provide the exact name definition and your assignment code. Just as a guess: You have to use the areas collection to loop through all areas of your range "bloycee" wrote: I've just used a named range to fill an array and have found a peculiarity that I'm not sure about. The named range was not contiguous and when assigning it to the array the array only appears to have used the second section of the range. Is this what normally happens and is there a work around? If I gave each part of the original range a separate name could I then read them easily in to the same array? How? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Range to Array Oddity
Thanks all,
I'll give Peter's idea a go. Cheers. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Named Range within an Array Formula | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
Named range into an array | Excel Worksheet Functions | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
Defined named range to array | Excel Programming |