Comparing Different Sized Arrays

[ExcelMonkey]

I have two arrays. Array1 is 2D. The array2 is 1D.
Array2 has a subset of the values from array1. As such
it will always have fewer rows than Array1. I am checking
to see which values from the array1 are actually in the
array2. I am using a Match statment. The Match
statement will register when a match is found and put 0
in the second column of the first array. However when a
match is not found the code fails. This is because the
array2 will always have fewer rows then array1 as it is
always a subset of the first. I put in a If Not IsError
stmt thinking that this would allow the code to
progress. However it is not working. I keep getting an
Error 1004 "Unable to get the Match property of the
worksheet function class. It fails when X = 6.


For X = 1 To 6
Z = Application.WorksheetFunction.Match(Array1(0, X -
1), Array2, 0)
If Not IsError(Z) Then
Array1(1, X - 1) = 0
Else:
Array1(1, X - 1) = 1
End If
Debug.Print Array1(0, X - 1) & " " & Array1(1, X - 1)
& " "; Z
Next

The immediate window shows:

Sheet1 0
Sheet2 0
Sheet3 0
Sheet4 0
Sheet5 0

I want it to show:

Sheet1 0
Sheet2 0
Sheet3 0
Sheet4 0
Sheet5 0
Sheet6 1

Is my IsError statement not the right approach here?

Thanks

 

[Myrna Larson]

Try writing the line as

Z = Application.Match(Array1(0, X - 1), Array2, 0)

I've found that often fixes the problems with MATCH (it's a bug!)

 

[ExcelMonkey]

YEah that worked. Been up all night trying to figure
that out. Who da thought?

Thanks!

 

[Myrna Larson]

Who da thought?

Us old-timers who were writing this kind of code before they added the
WorksheetFunction business to the mix <g>.

What is curious in your case is that the first 5 instances work correctly and
the last one doesn't. As they say, "go figure".