Dave Peterson wrote:
> I don't recall those functions exhibiting this problem.
Oh yes, indeed. Thanks for the code below. Another that doesn't work
in xl2000 because of the limit would be
res = .Vlookup(65500, myArr, 2, False)
Thanks again,
Alan Beban
>
> But I tried this:
>
> Option Explicit
> Sub testme()
> Dim myArr As Variant
> Dim res As Variant
>
> With ActiveSheet.Range("a1:b65536")
> .Formula = "=row()"
> .Value = .Value
>
> res = Application.Index(.Range("a:a"), 65500)
>
> Debug.Print "---.index---"
> If IsError(res) Then
> Debug.Print CStr(res)
> Else
> Debug.Print res
> End If
>
> res = Application.VLookup(65500, .Range("a:b"), 2, False)
>
> Debug.Print "---.vlookup---"
> If IsError(res) Then
> Debug.Print CStr(res)
> Else
> Debug.Print res
> End If
>
> res = Application.Match(65500, .Range("a:a"), 0)
>
> Debug.Print "---.match---"
> If IsError(res) Then
> Debug.Print CStr(res)
> Else
> Debug.Print res
> End If
>
> End With
>
> End Sub
>
> and got this:
> ---.index---
> 65500
> ---.vlookup---
> 65500
> ---.match---
> 65500
>
> And using the array:
>
> Option Explicit
> Sub testme()
> Dim myArr As Variant
> Dim res As Variant
>
> With ActiveSheet.Range("a1:b65536")
> .Formula = "=row()"
> .Value = .Value
> myArr = .Value
> End With
>
> With Application
> res = .Match(65500, .Index(myArr, 0, 1), 0)
> Debug.Print "---.match & .index---"
> If IsError(res) Then
> Debug.Print CStr(res)
> Else
> Debug.Print res
> End If
> End With
>
> End Sub
>
> I got:
>
> ---.match & .index---
> 65500
>
> =========
> Did I get close to what you were looking for?
>
> If no, if you have some code, I can paste it in and test it out.
>
> Alan Beban wrote:
>
>>Thanks, Dave,
>>
>>INDEX, MATCH and VLOOKUP were other affected functions.
>>
>>Alan Beban
>>
>>Dave Peterson wrote:
>>
>>>I remember that xl2002 removed the 5461 limit in application.transpose. And
>>>it's still gone in xl2003:
>>>
>>>Option Explicit
>>>Sub testme()
>>> Dim myArr As Variant
>>>
>>> With ActiveSheet.Range("a1:a65536")
>>> .Formula = "=row()"
>>> .Value = .Value
>>> myArr = .Value
>>> End With
>>>
>>> With Application
>>> myArr = .Transpose(.Transpose(myArr))
>>> End With
>>>
>>> Debug.Print UBound(myArr)
>>>
>>>End Sub
>>>
>>>showed:
>>>65536
>>>in the immediate window.
>>>
>>>Do you have a particular function you're concerned about?
>>>
>>>
>>>Alan Beban wrote:
>>>
>>>
>>>>In xl2000 there is a limit of 5461 elements on the efficacy of many
>>>>worksheet functions used in VBA code. Has that limit been changed in
>>>>later versions of Excel?
>>>>
>>>>Thanks,
>>>>Alan Beban
>>>
>>>
>
|
Similar Threads
