Hercules1946
New member
- Joined
- Mar 22, 2013
- Messages
- 794
- Reaction score
- 0
- Points
- 0
- Location
- York, England
- Excel Version(s)
- 2010
Hi
Ive been looking at a problem and Ive encountered something I don't understand. The formulae Im using on the attachment (Sheet 3) are to pull records from Sheet2 based on a Payment method of Cash or Cheque using FIND( to verify the entries inCol B of sheet2 (the "METH" range). To get more flexibility I created a UDF to check the payment methods (Fstr). Im using this in one cell (B5) on Sheet3. The others on Sheet 3 use the FIND function. The return value in B5 (Sheet3 is
wrong because there is a difference in the way the array filtering operates with the two functions and yet the data being passed looks exactly the same.
THere are two arrays of 30 elements
METH Array
TRUE = Cell Value either "Cash" or "Cheque"
FALSE = Cell Value not as above
Row Numbers Array {1 to 30} From ROW(METH)-4
An IF statement returns row no if TRUE and FALSE if FALSE.
Now - With the FIND function the matching is one for one between the two arrays, returning 30 elements, but line 1 is FALSE and so the SMALL function returns 2 ("Cheque") which is correct.
With the Fstr UDF it seems to be matching every line with every element in the METH Array, so 1 is returned which is wrong. Bearing in mind that the data appears identical in Evaluate formula
I cant understand whats causing this and how to correct it.
Could it be that Im only returning the values tested by the UDF and not the range objects ?
I know I can just leave the FIND in there, but the UDF will provide more functionality, and I would like to understand this. Id appreciate any help.
Ive been looking at a problem and Ive encountered something I don't understand. The formulae Im using on the attachment (Sheet 3) are to pull records from Sheet2 based on a Payment method of Cash or Cheque using FIND( to verify the entries inCol B of sheet2 (the "METH" range). To get more flexibility I created a UDF to check the payment methods (Fstr). Im using this in one cell (B5) on Sheet3. The others on Sheet 3 use the FIND function. The return value in B5 (Sheet3 is
wrong because there is a difference in the way the array filtering operates with the two functions and yet the data being passed looks exactly the same.
THere are two arrays of 30 elements
METH Array
TRUE = Cell Value either "Cash" or "Cheque"
FALSE = Cell Value not as above
Row Numbers Array {1 to 30} From ROW(METH)-4
An IF statement returns row no if TRUE and FALSE if FALSE.
Now - With the FIND function the matching is one for one between the two arrays, returning 30 elements, but line 1 is FALSE and so the SMALL function returns 2 ("Cheque") which is correct.
With the Fstr UDF it seems to be matching every line with every element in the METH Array, so 1 is returned which is wrong. Bearing in mind that the data appears identical in Evaluate formula
I cant understand whats causing this and how to correct it.
Could it be that Im only returning the values tested by the UDF and not the range objects ?
I know I can just leave the FIND in there, but the UDF will provide more functionality, and I would like to understand this. Id appreciate any help.