How to enumerate a variant array?

[stranno]

Hi,

Does anyone know why the code below raises an error?

[vba]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
[/vba]
Greetz,
Stranno

[snb]

[vba]
For i = 1 To UBound(myArr)
MsgBox myArr(i, 1)
Next
[/vba]

[shrivallabha]

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

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

[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:
[VBA]Var = Application.Transpose(Range("A1:A65536").Value)
[/VBA]
and following doesn't:
[VBA]Var = Application.Transpose(Range("A1:A65537").Value)
[/VBA]

[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.