arrays

chris2727272727

Board Regular
Joined
Jul 10, 2005
Messages
152
what the best way to assign the value of cell A1: a50 to a 50 variant array ie a3 = myarray(3) etc etc
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
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.
Code:
myarray
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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:

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
Whoops major error in my post.:oops:

Second part should be this.
Code:
myarray = Application.WorksheetFunction(Range("A1:A50"))
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
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.:)
Code:
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

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
Do you mean that you should just learn to handle the 2d array that is created with this?

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

Legend
Joined
Feb 17, 2002
Messages
9,700
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... :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,169
Messages
5,570,654
Members
412,335
Latest member
cinciri99
Top