Problem with array formula and user defined function

[Hercules1946]

I have an array formula thats pulling the lines from sheet A to sheet B based on a criteria column in A If the criteria says "Cash" or "Cheque" that data is pulled onto B.
Excel builds two arrays one showing the contents of the A criteria column and the other a set of TRUE and FALSE indicating (correctly) whether the criteria is met.
SMALL then works with the row numbers to find the lowest row number for the TRUE criteria routed by IF(TRUE)

(a) Both the row numbers array and the (TRUE/FALSE) array (30 elements in each) are correctly listed and lined up with each other by Evaluate Formula. Now I can state my problem.

If Im using FIND( to do the criteria test, then the correct row is returned. If I use my UDF it isn't. Everything is the same at point (a) with both functions.
The FIND route matches the two arrays on a one for one basis giving me 30 elements returning the line numbers (TRUE) and returning FALSE (FOR FALSE). (SMALL returns the correct row).
Th UDF route matches all the TRUE/FALSE with every entry in the row numbers so that SMALL picks 1 which is always available along with all the others

For info, my UDF uses Instr to check that the tested value is in the correct section of the (lookup) string of values (eg having a startpoint at position before <=5, as in "CashCheque....." ).

Why are the arrays handled differently by the two functions ? How can I get the UDF to work like FIND? Ive tried everything I can think of on this with no luck.

Any help would be appreciated.

This is also posted at:
Problem with Look Up Array Formula

will advise when resolved

 

[Andrew Poulsom]

Please post your UDF.

 

[Hercules1946]

Please post your UDF.

Hello Andrew
Thanks for your response.
Here is the coding for the UDF:

Public Function Fstr(MyRange As Range) As Variant
Dim TestString As String, Mystring As String
TestString = "CashChequeEFTPOS"
Dim i As Long, rng As Variant, Arr() As Variant
i = MyRange.Cells.Count
ReDim Arr(1 To i)
i = 1
For Each rng In MyRange
    Arr(i) = InStr(TestString, rng.Value)
    i = i + 1
Next rng
Fstr = Arr

End Function

 

[mikerickson]

What is the array formula that fails?
It looks to me that your UDF will return a row-wise array.
Might using TRANSPOSE(Fstr(someRange)) fix the issue?

Or perhaps this, which returns an array the same shape as myRange.

Function Fstr(MyRange as Range) As Variant
Dim TestString As String, Mystring As String
TestString = "CashChequeEFTPOS"
Dim i As Long, j As Long, rng As Variant, Arr As Variant

If myRange.Cells.Count = 1 Then
    Redim Arr(1 to 1, 1 to 1)
    Arr(1, 1) = InStr(TestString, myRange.Value)
Else
    Arr = myRange.Value:Rem equivalent of ReDim

    For i = 1 to myRange.Rows.Count
        For j = 1 to myRange.Columns.Count
            Arr(i,j) = InStr(TestString, myRange.Cells(i, j).Value)
        Next j
    Next i
End If
 
Fstr = Arr

End Function

 

[Hercules1946]

Hello Andrew/Mike
Here are some shots from my spreadsheet. Basically I started off using FIND which seemed to work OK as shown in B6 of the Extract Sheet (The second shot). The B cells should only return "Cash" or "Cheque" from the Source sheet (the first shot).
As a trial, I used the UDF Fstr in B5 and that returns EFTPOS which is wrong. Dont pay any attention to cols C to E as they may confuse. Col B is the problem

Source Table with the Named Ranges

Excel 2010
	B	C	D	E
3	Payment Method	Date	Receipt #	Name
4
5	EFTPOS	30/10/13	230	Fred Smith
6	Cheque	30/10/13	231	John Doe
7	EFTPOS	30/10/13	232	Luke Harris
8	Cash	31/10/13	233	Darren Loser
CASH BOOK

Extract Table:

Excel 2010
	B	C	D	E
3	Payment Method	Date	Receipt #	Name
4
5	EFTPOS	30/10/13	231	John Doe
6	Cash	31/10/13	233	Darren Loser
7	Cheque	1/11/13	234	Brad Walks
8	Cash	2/11/13	236	Jane Doe
9	Cheque	2/11/13	237	Hanry Whinst
10	Cheque	2/11/13	238	Fred Brown
Cash Book - Cash & Chq

Cell Formulas
Range	Formula
B5	{=IFERROR(INDEX(METH,SMALL(IF(FSTR(METH) <= 5, ROW(METH)-4),ROWS(B$4:B4))),"")}
B6	{=IFERROR(INDEX(METH,SMALL(IF(FIND(METH,PM,1) <= 5, ROW(METH)-4),ROWS(B$4:B5))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
METH	='CASH BOOK'!$B$5:$B$34
PM	=Sheet1!$D$3

 

[Andrew Poulsom]

What's in D3 on Sheet1?

 

[Hercules1946]

Sorry - ran out of edit time.

The Range PM = "CashChequeEFTPOS" (Sheet1!D3)

If you need anything else, let me know

Thanks
Hercules

 

[Andrew Poulsom]

The last line of your UDF should be:

Fstr = Application.Transpose(Arr)

 

[Hercules1946]

The last line of your UDF should be:

Fstr = Application.Transpose(Arr)

Thanks very much Andrew, thats fixed it!!

 

[mikerickson]

Did you try my variation of the UDF?
TRANSPOSE (either in the UDF or in the formula) shifts the problem to the case where you use Fstr on a A1:Z1.