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
camper12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony BerenguelCommented:
I don't see the attachment
camper12Author Commented:
camper12Author Commented:
attached. sorry for that!
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Anthony BerenguelCommented:
I'm not sure what you want to happen when the formula is entered in the first row but...AB1.xlsx
byundtMechanical EngineerCommented:
If you want to get the value of a cell two columns to the left of the one calling MyVBAfunction, then you could use code like shown below. Note that you need to format the cell in column C with a Date format, otherwise you will see a number like 42037.
Function MyVBAfunction() As Variant
Dim cel As Range
Set cel = Application.Caller
MyVBAfunction = cel.Offset(0, -2).Value
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
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.
byundtMechanical EngineerCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.