populate array

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Martin Fishlock]

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
--

You may need to use the redim statement if you need to change the size.
Check out F1 for help.


HTHs Martin

 

[Marina Limeira]

How populate array by sheet data ?

example:

a1 = "mary"
a2 = "john"


in VBA

var AR = array( populate from A1 and A2 )


thanks

Marina

 

[Dave Peterson]

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.