Solved: Range to Array

[jmenche]

Howdy,

I am a newbie at arrays and would love for someone to tell me what's wrong with this.

I get a Subscript Out Of Range error when I run this.

Sub MyArray()
Dim MyArray
Dim i As Integer

MyArray = Range("BrandsFootcare").Value

For i = 1 To UBound(MyArray)

Debug.Print MyArray(i)

Next
End Sub

When I mouse over UBound I get the right number of items that should be in there. I also changed my Option Base to 1.

:beerchug:

[Norie]

When you create an array from a range it's normally 2-dimensional.

When using UBound if you don't specify a dimension it defaults to the 1st dimension.

You are getting the subscript out of range because you aren't specifying a 2nd dimension here.

Debug.Print MyArray(i)

[Bob Phillips]

Try this



Sub MyArray()
Dim MyArray
Dim i As Integer

MyArray = Application.Transpose(Range("BrandsFootcare"))

For i = 1 To UBound(MyArray)

Debug.Print MyArray(i)

Next
End Sub

[compariniaa]

or try
For i = 1 to UBound(MyArray)
Debug.Print MyArray(1,i)
'as firefyter has pointed out....it should be MyArray(i,1) rather than MyArray(1,i)
Next i'm not great with arrays, so i'm not 100% sure that will work

[Zack Barresse]

Yes, arrays directly translate to what your range is that you set it by (as you have done). Since the column of the array is generally assumed and not given, if you set by a range going down rows, you'll have an array of the (i, 1) type (assuming a single 2-D array); whereas if you have your data across columns you'll have an array of the (1, i) type (same assumption). Bob made use of the Transpose function whereby leaving that assumtion out.

[jmenche]

Thanks!

I added a 2nd dimension as ,1 and it worked fine.