Trying to mimic row() in custom VBA function

[joshg1]

I am new to VBA custom functions so there may be an obvious solution to this, but there may not be. Looking for some pointers... here goes!

I am currently creating a custom function that is calculated based on the row of each cell. If I was using the built in worksheet functions, I could enter

=row()

into a cell, say A1, and I will get the row of that cell, in this case 1. If I drag this formula down to B1, the formula will give a value of 2.

Is there a way to mimic this functionality in VBA? I initially tried using the below code, but found that the code does not work when dragging the formula down to other cells because the active cell is unchanged. The value only changes if I click in the cell B2 (thus changing the Active Cell) then enter the formula. Thanks in advance for any help you can give!

Please Login or Register  to view this content.

[romperstomper]

You want Application.Caller.Row

Can't imagine any situation where this would be a good approach though?

[stnkynts]

Do you mean like this?

Please Login or Register  to view this content.

[joshg1]

You want Application.Caller.Row

Can't imagine any situation where this would be a good approach though?

This did the trick. Thank you!