arrays

[chris2727272727]

what the best way to assign the value of cell A1: a50 to a 50 variant array ie a3 = myarray(3) etc etc

 

[Norie]

Try this.

Dim myarray As Variant
myarray = Range("A1:A50")

It doesn't actually assign/create a 50 variant array it creates a 50x1 array.

To get a 50 variant array try this.

myarray

 

[NateO]

I'll add an example to Norie's fine advice, also you might want to add ()s to force the Variable to be an array:

Sub foo()
Dim tmpArr() As Variant
Let tmpArr = Range("a1:a50").Value
MsgBox tmpArr(3, 1)
End Sub

 

[Norie]

Whoops major error in my post.

Second part should be this.

myarray = Application.WorksheetFunction(Range("A1:A50"))

 

[NateO]

Hello Norie,

Why refer to the WorksheetFunction Class? That gives me a run-time error: 'Object doesn't support this property or method'.

I think my example should resolve the OPs original question, yes-no?

 

[Norie]

Sorry again another error in me posting.

It's meant to be using the Transpose worksheet function.

I'll need to be a bit less trigger happy with that submit button.

myarray = Application.WorksheetFunction.Transpose(Range("A1:A50"))

By the way I'm not 100% sure if either of our solutions is what the OP wants.

I think they might have already declared and dimensioned the array.

And you can't assign to an array.

 

[NateO]

It looks like they just want to quick-stack a non-dimensioned array?

Excel's Transpose function can be handy if you need it, fortunately, most of the time you don't have to work with a 1-d array (you do if you plan on using the Join Function).

It's also fairly expensive, you only want to Transpose if you have to, not for the sake of doing it.

 

[Norie]

Do you mean that you should just learn to handle the 2d array that is created with this?

Sub foo() 
Dim tmpArr() As Variant 
Let tmpArr = Range("a1:a50").Value 
MsgBox tmpArr(3, 1) 
End Sub

If so I totally agree, but as you pointed out there is sometimes a need to convert to a 1d array.

Let's see what the OP's opinion is of our posts, and perhaps they can shed some light on what they are actually doing.

 

[NateO]

That is precisely what I mean. If you don't need to transpose, which you don't for Lookups, etc... Then don't, it could double-up your processing time...