Comparing Different Sized Arrays

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

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!)


On Sat, 19 Feb 2005 14:11:23 -0800, "ExcelMonkey"
<[email protected]> wrote:

>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

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

Thanks!


>-----Original Message-----
>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!)
>
>
>On Sat, 19 Feb 2005 14:11:23 -0800, "ExcelMonkey"
><[email protected]> wrote:
>
>>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
>
>.
>

On Sat, 19 Feb 2005 15:50:08 -0800, "ExcelMonkey"
<[email protected]> wrote:

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