How populate array by sheet data ?
example:
a1 = "mary"
a2 = "john"
in VBA
var AR = array( populate from A1 and A2 )
thanks
Marina
Bob Phillips - 20 Jan 2006 08:41 GMT
A couple of ways for you
ary = Array(Range("A1").Value, Range("A2").Value)
For Each cell In Selection
ReDim Preserve ary(i)
ary(i) = cell.Value
i = i + 1
Next cell
both of which produec a single dimension, base 0, array,
OR
ary = Selection
which creates a 2D, base 1, array

Signature
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
> How populate array by sheet data ?
>
[quoted text clipped - 10 lines]
>
> Marina
Martin Fishlock - 20 Jan 2006 08:51 GMT
Marina there are at least two was to populate an array depending upon the
variable.
If you use the variant and the array function try:
dim AR as variant
AR = array(activesheet.range("A1:A2")
or the traditional dim array:
dim AR(2) as string ' assuming strings
AR(1)=activesheet.range("A1")
AR(2)=activesheet.range("A2")
or for bigger arrays
dim AR(2) as string ' assuming strings
dim i as long
for i = 1 to 2
AR(i)=activesheet.cells(i, 1)
next i

Signature
You may need to use the redim statement if you need to change the size.
Check out F1 for help.
HTHs Martin
> How populate array by sheet data ?
>
[quoted text clipped - 10 lines]
>
> Marina
Dave Peterson - 20 Jan 2006 17:55 GMT
Option Explicit
Sub testme02()
Dim myArr As Variant
myArr = ActiveSheet.Range("a1:a2").Value
'myArr = Application.Transpose(ActiveSheet.Range("a1:a2").Value)
End Sub
The first version actually makes a 2 row by 1 column array.
The second (application.transpose version) makes it a single dimension array.
> How populate array by sheet data ?
>
[quoted text clipped - 10 lines]
>
> Marina

Signature
Dave Peterson