User Defined Function Returns Incorrect Data

[obb1uk]

I have written an UDF to search for a string "ITT" or "SUB" on each Row and it should
Return the Date in Row 1 corresponding to the where the data is found in
Example if I enter AOBF("ITT") in cell A2, it returns 01-Apr-18

Whenever I CopyDown A2 to A3:A5, it givs me the same result, i.e 01-Apr-18
However, when I Select each Cell in A2:B5 and press F2 followed by Enter, I get the correct results.
Could you please look at the UDFcode below and advise me where I have gone wrong.
Note: the AOBF_Update Macro and macro Button woks fine.

sample data
ITT SUB PROJECT 01-Apr-18 02-May-18 03-Jun-18
01-Apr-18 F1355 ITT
01-Apr-18 03-Jun-18 F1356 SUB
01-Apr-18 03-Jun-18 F1357 ITT
01-Apr-18 03-Jun-18 F1358 ITT


UDF Code

Function AOBF(sstr As Variant) As Variant
'Macro by O. Batchelor
'To search the current Row looking for sstr and put Date in the Col1 of Row into ActiveCell
Dim found As Range
Dim rno As Long
rno = ActiveCell.Row
AOBF = ""
qfind:
Set found = ActiveSheet.Rows(rno).Find(what:=sstr, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If found Is Nothing Then
AOBF = ""
Exit Function
End If
If IsDate(Cells(1, found.Column).Value) Then AOBF = Cells(1, found.Column).Value ' Return Date at Column 1
sstr = ""
End Function

<colgroup><col><col><col span="2"><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

Yes Peter, I have now read all the threads and got the solution to why the UDF didn't work.
I am now very happy but I have opted for your excel formula instead.
Thanks again for your help.

No problem.

BTW, When I wrote the suggested formula I was largely trying to follow what you had in your UDF. In fact, I suspect that you could use this simpler formula?

=IFERROR(INDEX(B$1:Z$1,MATCH("*ITT*",B2:Z2,0)),"")

 

[obb1uk]

Good morning Peter.

That is shorter and easier on the eye, so I will implement this method.
Thanks again, most appreciated.