asked on

get row number

Hi Experts,

I have attached a sample dummy excel sheet. I have a VBA formula function in column C. What I want is to get the date in column A for a particular row. For my third row, I would want MyVBAFormula to get the date in cell A3. For my second row, I would want MyVBAFormula to get the date in cell A2. How can I program for this?

Thanks

Anthony Berenguel

I don't see the attachment

camper12

ASKER

1.xlsx

camper12

ASKER

attached. sorry for that!

Anthony Berenguel

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

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

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

Open 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.

byundt

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

Open in new window