E
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
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