Loading an Array

[Chad Cameron]

Hi All,

Everywhere I look, it appears I have to manually load an array ex: a =
array(100, 101, 102 ...)

Is there a way to do it with code? I need 50 items in the array but the
data is always changing.
What I need is:

MyArray(1) = Activecell.offset(1,0)
MyArray(2) = Activecell.offset(2,0)

etc.

When I try, I get a type mismatch error.

Thanks
Chad

 

[Chad]

Got it,

I forgot to declare it properly

Dim MyArray(50) as Variant

not

DimArray as Variant

 

[Per Jessen]

Everywhere I look, it appears I have to manually load an array ex: a =
array(100, 101, 102 ...)

Is there a way to do it with code? I need 50 items in the array but the
data is always changing.
What I need is:

MyArray(1) = Activecell.offset(1,0)
MyArray(2) = Activecell.offset(2,0)

etc.

When I try, I get a type mismatch error.

Thanks
Chad

Hi Chad
MyArray(1)=activecell.offset(1,0).value

or

for c = 1 to 10
MyArray(c)=activecell.offset(c,0).value
next

Regards

Per

 

[Dana DeLouis]

Just to mention an alternative...

Sub Demo()
Dim MyArray()
MyArray = ActiveCell.Resize(50, 1)

'And...if you wish:

MyArray = WorksheetFunction.Transpose(MyArray)
End Sub