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.

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

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.

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.

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.

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.

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