get row number

I don't see the attachment

1.xlsx

attached. sorry for that!

I'm not sure what you want to happen when the formula is entered in the first row but...AB1.xlsx

The fact that a UDF can get a value from the same row as the cell calling it does not make doing so a good idea. When you do that, the UDF will not update its value if the date in column A changes.

The lack of an update problem can be overcome by making the function volatile, but then it updates values whenever anything changes in any open workbook.

Function MyVBAfunction() As Variant
Dim cel As Range
Application.Volatile
Set cel = Application.Caller
MyVBAfunction = cel.Offset(0, -2).Value
End Function

Select allOpen in new window

Best practice suggests that you abandon the approach you are proposing, and pass the value in column A as an additional input to your UDF. If you do that, the UDF formula will update when it needs to, and only when it needs to.

If you want a value from column A in the same row as the UDF formula--but the UDF formula might be in any column, then you would use:

Function MyVBAfunction() As Variant
Dim cel As Range
Application.Volatile
Set cel = Application.Caller
MyVBAfunction = cel.EntireRow.Cells(1, 1).Value
End Function

Select allOpen in new window