How to enumerate a variant array?

[stranno]

Hi,

Does anyone know why the code below raises an error?

Sub arrArray()
Dim myArr As Variant
Dim a

For i = 1 To 100
ActiveSheet.Cells(i, 1) = i
Next

myArr = ActiveSheet.Range(Cells(1, 1), Cells(ActiveSheet.Rows.Count, 1).End(xlUp)).Value2

'This code works fine
i = 0
For Each a In myArr
i = i + 1
Cells(i, 2) = a
Next

'What's wrong with this code
For i = 1 To UBound(myArr)
MsgBox myArr(i)
Next

End Sub

Greetz,
Stranno

[snb]

For i = 1 To UBound(myArr)
MsgBox myArr(i, 1)
Next

[shrivallabha]

Try also:
myArr = ActiveSheet.Range(Cells(1, 1), Cells(ActiveSheet.Rows.Count, 1).End(xlUp)).Value2

to
myArr = Application.Transpose(ActiveSheet.Range(Cells(1, 1), Cells(ActiveSheet.Rows.Count, 1).End(xlUp)).Value2)

[stranno]

Thank 's snb and shrivallabha you 've refreshed my memory.
By the way, is the transpose function still limited to 65536
elements in the latest version of Excel?

[snb]

I'm very interested in the results of your test.

[shrivallabha]

Thank 's snb and shrivallabha you 've refreshed my memory.
By the way, is the transpose function still limited to 65536
elements in the latest version of Excel?

I have 2007 following works:
Var = Application.Transpose(Range("A1:A65536").Value)

and following doesn't:
Var = Application.Transpose(Range("A1:A65537").Value)

[stranno]

"I'm very interested in the results of your test."

Both solutions worked fine!

Don't know how to mark "Solved"

shrivallabha, you figured it out for Office 2007. I work with Office 2010 and
get de same result. Probably a remain of the older versions. But strange it is!

[stranno]

snb,
I visited http://www.snb-vba.eu and bookmarked it.
It 's a very helpfull site!

[snb]

Thank you ! :)

[shrivallabha]

Don't know how to mark "Solved"


Hi stranno,

The option can be found under "Thread Tools" which says "Mark as Solved".

Note:= It has some browser issues as well e.g. you won't be able to see it in Google Chrome. Use Internet Explorer to see this option.