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>

 

[offthelip]

I think your problem is you want to start your search from the cell address that the UDF is written into NOT the ACTIVE cell
to do this you need to use the following code to find where it is called from:
Calladdres=Application.Caller.Address
Fom this you can get the row to start on.

 

[Osvaldo Palmeiro]

Try after replacing this line
rno = ActiveCell.Row

by this
rno = Application.Caller.Row

 

[Peter_SSs]

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

I'm wondering why you need to use a UDF? Couldn't you use a standard worksheet formula along the lines of

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

 

[obb1uk]

Good afternoon Peter.

Your solution works just fine.
I will spend a little time trying to decifer it.
Many thanks for finding an alternative method to solving my query.
NB... I just wondered if you were able to see why my UDF didn't work properly.

 

[Peter_SSs]

Good afternoon Peter.

Your solution works just fine.
I will spend a little time trying to decifer it.
Many thanks for finding an alternative method to solving my query.

You're welcome.

NB... I just wondered if you were able to see why my UDF didn't work properly.

Hasn't that already been answered earlier in the thread?

 

[obb1uk]

Thank you very much. It now works perfectly and I have learnt something new.

 

[obb1uk]

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.

 

[obb1uk]

Thanks Osvaldo, Your solution works just fine.
I am now a happy man...

 

[obb1uk]

Many thanks, Your solution works just fine.
I am now a happy man...