Use of Address Formula within a UDF

[SimonGeoghegan]

Hi All,

I have a spreadsheet i'm using which has dates within different columns. I need to be able to identify the last cell in each row, which I have managed to do via:

=ADDRESS(ROW(A3),MAX((D3:V3<>"")*COLUMN(D3:V3)),1)

For example, providing me with "$E$3"

From there, I have a UDF called "GetComment" (which I found from this site) that extracts the text from a comment within this cell. Unfortunately, when I wrap this UDF around the above code, it presents as #VALUE!.

If I use the UDF with the cell reference, it works;

=getComment(E3)

However, used with the Address formula above, it doesnt work;

=getComment(ADDRESS(ROW(A3),MAX((D3:V3<>"")*COLUMN(D3:V3)),1))

Does anyone have any ideas on this at all? Perhaps there is something I need to change within the UDF to refine it?

UDF Code below.

Function getComment(xCell As Range) As String
'UpdatebyExtendoffice20180330
On Error Resume Next
getComment = xCell.Comment.Text
End Function

Help appreciated as always, thanks.
Simon

 

[Fluff]

You are passing the function a string & not a cell, so try

Function getComment(xCell As String) As String
On Error Resume Next
getComment = Range(xCell).Comment.Text
End Function

 

[SimonGeoghegan]

Hi Fluff - that has worked a treat. Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.