How to use the rownumber of the current cell in a formula

W

Wim Goelen

Hi,

is there a way to retrieve the rownumber of the cell that "has the focus"? I
would like to calculate a number of values in the top row, but the formulas
need the rownumber to look up the correct prices.

In the formula below, the cell will contain a value of 50, 20 or zero,
depending on where the user's cursor is placed, the rownumber is then put in
D5

=IF(INDIRECT(CONCATENATE("H";D5))>0;50;IF(INDIRECT(CONCATENATE("L";D5))>0;20;0))

Thanks,

Wim Goelen
 
M

Michael

After you hit Alt F11, when you see the vba Project window on the left hand
side of your screen click on the Sheet Name that you want to add the code on
For example Sheet1 and the right hand side select from the first dropdown box
Worksheet and from the second dropdown box choose selection change and paste
the code below:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MyRow = Target.Row
Range("A20").Value = MyRow
End Sub
Then on your formula point to A20 to provide the active row number.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top