get row number

camper12
camper12 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't see the attachment

Author

Commented:

Author

Commented:
attached. sorry for that!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

I'm not sure what you want to happen when the formula is entered in the first row but...AB1.xlsx
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
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

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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 Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial