Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Different Sized Arrays
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Different Sized Arrays
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" 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Different Sized Arrays
On Sat, 19 Feb 2005 15:50:08 -0800, "ExcelMonkey"
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". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use linest with variably sized data arrays? | Excel Worksheet Functions | |||
Comparing Arrays | Excel Worksheet Functions | |||
Comparing to Arrays | Excel Programming | |||
Comparing to Arrays | Excel Programming | |||
Printing a legal sized document on letter sized paper | Excel Programming |