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
<colgroup><col><col><col span="2"><col><col><col><col span="3"></colgroup><tbody>
</tbody>
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>