View Full Version : How to enumerate a variant array?
stranno
10-06-2012, 05:09 AM
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
For i = 1 To UBound(myArr)
MsgBox myArr(i, 1)
Next
shrivallabha
10-06-2012, 06:45 AM
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
10-06-2012, 07:50 AM
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'm very interested in the results of your test.
shrivallabha
10-06-2012, 08:09 AM
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
10-06-2012, 08:32 AM
"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
10-06-2012, 08:39 AM
snb,
I visited http://www.snb-vba.eu and bookmarked it.
It 's a very helpfull site!
shrivallabha
10-07-2012, 03:08 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.