Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quickest Way of making an Array
1
2 3 4 5 6 What is the quickest way of putting the above into an array? The above numbers are located on sheet1 range("A1:A6") I use what I believe is the normal way of filling the array i.e a For Next Loop - but maybe there are better ways of going about it? Regards, Jason. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quickest Way of making an Array
You can pick up a range directly from a range in a worksheet with something
like: Option Explicit Sub testme() Dim myArr As Variant Dim iCtr As Long myArr = ActiveSheet.Range("a1:a6").Value For iCtr = LBound(myArr, 1) To UBound(myArr, 1) MsgBox myArr(iCtr, 1) Next iCtr End Sub But notice that the array is actually 2 dimensions--rows by columns. In this case, it's a 6 x 1 array. If you want to make it a single dimension array, you could use application.transpose() to pick it up. Be aware that in xl2k and below (IIRC), application.transpose is limited to 5461 elements. xl2002+ can use the whole column. Sub testme2() Dim myArr As Variant Dim iCtr As Long myArr = Application.Transpose(ActiveSheet.Range("a1:a6").V alue) For iCtr = LBound(myArr) To UBound(myArr) MsgBox myArr(iCtr) Next iCtr End Sub WhytheQ wrote: 1 2 3 4 5 6 What is the quickest way of putting the above into an array? The above numbers are located on sheet1 range("A1:A6") I use what I believe is the normal way of filling the array i.e a For Next Loop - but maybe there are better ways of going about it? Regards, Jason. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quickest Way of making an Array
If you know that the range will always be A1:A6 and you don't mind having a
long line of code, you can use: myArray = Array(Range("A1").Value, Range("A2").Value, Range("A3").Value, Range("A4").Value, Range("A5").Value, Range("A6").Value) -- Thanks, Mike "WhytheQ" wrote: 1 2 3 4 5 6 What is the quickest way of putting the above into an array? The above numbers are located on sheet1 range("A1:A6") I use what I believe is the normal way of filling the array i.e a For Next Loop - but maybe there are better ways of going about it? Regards, Jason. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quickest Way of making an Array
Jason,
Slightly different than Dave's. Apparently, without the Transpose, even a one-dimensional array is treated as multi-dimensional when created from a range. The Transpose removes the multi-dimensional quality, so the reference is to v(i) rather than v(i,1): Sub test() Dim v As Variant Dim i As Long v = Application.Transpose(ActiveSheet.Range("A1:A6")) For i = LBound(v) To UBound(v) Debug.Print "v(" & i & ") = " & v(i) Next i End Sub hth, Doug "WhytheQ" wrote in message ups.com... 1 2 3 4 5 6 What is the quickest way of putting the above into an array? The above numbers are located on sheet1 range("A1:A6") I use what I believe is the normal way of filling the array i.e a For Next Loop - but maybe there are better ways of going about it? Regards, Jason. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quickest Way of making an Array
if you don't need commas in between
ma = ActiveSheet.Range("a1:a6").Value -- Don Guillett SalesAid Software "WhytheQ" wrote in message ups.com... 1 2 3 4 5 6 What is the quickest way of putting the above into an array? The above numbers are located on sheet1 range("A1:A6") I use what I believe is the normal way of filling the array i.e a For Next Loop - but maybe there are better ways of going about it? Regards, Jason. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quickest Way of making an Array
Dim v as Variant
v = Range("A1:A6") will put it in a two dimensional base 1 array (regardless of Option Base setting) Dim v as Variant v = Range("A1:A6") for i = 1 to 6 msgbox "i" & ", " & v(i,1) Next --------- This will put it in a 1 D array: Sub MakeArray() Dim v As Variant v = Application.Transpose(Range("A1:A6")) For i = 1 To 6 MsgBox "i" & ", " & v(i) Next End Sub -- Regards, Tom Ogilvy "WhytheQ" wrote in message ups.com... 1 2 3 4 5 6 What is the quickest way of putting the above into an array? The above numbers are located on sheet1 range("A1:A6") I use what I believe is the normal way of filling the array i.e a For Next Loop - but maybe there are better ways of going about it? Regards, Jason. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quickest Way of making an Array
The first array below will be a "vertical" array; the second a
"horizontal" array. If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your file, you can produce a two-dimensional "horizontal" array with v = ArrayTranspose(Range("A1:A6")) Alan Beban Tom Ogilvy wrote: Dim v as Variant v = Range("A1:A6") will put it in a two dimensional base 1 array (regardless of Option Base setting) Dim v as Variant v = Range("A1:A6") for i = 1 to 6 msgbox "i" & ", " & v(i,1) Next --------- This will put it in a 1 D array: Sub MakeArray() Dim v As Variant v = Application.Transpose(Range("A1:A6")) For i = 1 To 6 MsgBox "i" & ", " & v(i) Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
making sure a cell in an array is not duplicated | Excel Discussion (Misc queries) | |||
IRR: is there a quickest way? | Excel Worksheet Functions | |||
Making Array Formula, please assist! | Excel Worksheet Functions | |||
Making array from range | Excel Programming | |||
Making progress with array functions, another two questions | Excel Programming |