VBA Offset (trying to resize an element within nested array)

[ker_01]

I'm running into a problem with application.match, and I'm thinking it may be
because my comparison array is an array equivalent to a whole column in 2007
(1MM+ rows) so I want to shorten my array to only include the area with
values.

However, my attempts to shrink the nested array have so far been unsuccessful;

Public AllRanges(1 To 10) As Variant

Sub SetRange_DemoForNewsgroupPosting
'here is code without all the extra errorchecking and processing lines
Set TempRange = Application.InputBox(Prompt:="Column?", Title:="Demo",
Type:=8)
Set PassRange = TempRange.Columns(1).EntireColumn
AllRanges(2) = PassRange.Value
End Sub

Sub ShrinkMyArray
Arr2Count = 528
Set AllRanges(2) = AllRanges(2)(1, 1).Offset(0, 0, Arr2Count, 1)
End Sub

For what it's worth, the reason I'm doing all this is because this next line
of code works on workbookA on Excel2003, but when run on workbookB on
Excel2007, it gives a runtime error 13, type mismatch:

RowMatch = Application.Match(AllRanges(1)(i, 1), AllRanges(2), False)
'AllRanges(1)(i, 1) confirmed as an expected string, so the problem has to
be with AllRanges(2). Using debug.print I confirmed that Allranges(2) is
populated with the expected data... so my best guess is that the array size
is just too large for application.match

Thank you!
Keith

 

[Joel]

I would use the following:

from:
AllRanges(1)(i, 1)
to:
AllRanges(1).offset(i, 1)

AllRange will automtically return the 1st cell in the range.

I'm not sure why you are using MATCH when you can use find instead.
from
RowMatch = Application.Match(AllRanges(1)(i, 1), AllRanges(2), False)

to set c = Range(AllRanges(2)).find(what:=AllRanges(1).offset(i, 1), _
lookin:=xlvalues,lookat:=xlwhole)