# current cell position in Excel

Hi,
I am looking for a formula that will evaluate the current (active) cell's row number.

For instance, let's say I write this formula in cell A1.
Now when I click on cell B6 (or move to it with arrow keys), cell A1 should show 6. If I go to E10, it should show 10 etc.

Incredible, but I can't find a simple solution. I even wrote VBA code as given below for my function, and wrote the formula
=currow()
in cell A1, but the formula does not update automatically as I move the active cell around.

- stochastic

the code I wrote:

Public Function currow() As Integer
Application.Volatile
currow = ActiveCell.Row
End Function
LVL 8
###### Who is Participating?

Commented:
Here'a an alternative to Bruintje's formula. It returns the row number of the active cell, provided that you have a Calculate statement in the Worksheet_SelectionChange event procedure.

/Ture
0

Commented:
stochastic,

Put this code in ThisWorkbook to force a recalculation whenever the selection changes.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Range("A1").Calculate
End Sub

Put this function in a module

Function CurrentRow()
Application.Volatile
CurrentRow = ActiveCell.Row
End Function

Now, use the function in any cell in the workbook.
=CURRENTROW()

Happy?

Ture Magnusson
0

Author Commented:
Ummm...

Thanks, Ture. I knew I could count on you.

Still, I was hoping that there's a better solution than handling a selection change event. Back in the good old days of Lotus 123, I think there used to be a worksheet function called cellpointer() or something, that had a lot of power. Looks like Excel doesn't have an equivalent!

This will, surely, serve my purpose, but may slow down things a bit. (I need to use this in a simulation problem with intense calculation).

The points are yours - I'm quite certain that if YOU say there isn't a better solution, then there isn't!

Nevertheless, please permit me to keep this question open for a couple of days, just to see if any other interesting responses come through.

Anyone else giving other ORIGINAL ideas - I'll happily part with 100 points apiece, or more.

thanks,
stochastic
0

Commented:
stochastic,

Thanks for the feedback. Unfortunately, this is the only way I know of, but there may be other solutions. Yes... let's keep the question open for a while.

/Ture
0

Commented:
Hi
Write this code in VBA

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("a1").Value = ActiveCell.Row
End Sub

Rgds

Harsha
0

Author Commented:
Harsha,

Thanks for your help, but I'm afraid I must reject your answer because both the ideas you proposed, namely,
b. using activecell.row property

It's normally a practice here in EE to post suggestions as comments and not as an answer, even if you are fairly confident of your suggestion being the right one.

- stochastic

0

Commented:

i did use it with

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Calculate
End Sub

maybe you could make the cell reference a it prettier then \$A\$9

but it's at least an alternative

:O)Bruintje
0

Commented:
all right we got the row number only it's ugly but fun since i use the dutch version

should be something like

I'm not sure about the instring it's the funtion to find a character in a string, and i don't know the english equivalent for it sorry :O)

the sheetchange funtion was

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub

this was a fun one like in the old days

Sleepzzzz
:O)Bruintje
0

Commented:
btw the instring should be SEARCH i guess so it would be like

0

Author Commented:
Bruintje,

The Dutch part was fun! Apart from the changes in function names, I also have to contend for ; versus , differences! After doing all that, I get #NUM for an answer!

But don't worry. I got the point, and I'll soon try it out.

About commas and semicolons and decimal points: they have driven me crazy for months, with a product developed in VC++ that I had to Internationalize! It's incredible how much these things differ from Nordic to Belgian to Belgian French to French French!

If I were Asterix, I would say "These Europeans are Crazy!"
(No, I didn't mean that seriously; it was just fun). Before I spark off a regional war, let me hasten to clarify that I am NOT American, I am in fact far, far eastward from all western influences.

thanks, and I'll be back soon with more to report.
- stochastic

0

Commented:
well we do have our regional wars already, the Balkans, the Basks in Spain, the North-South politics in Italy, the Dutch-French divide in Belgium which is split up on those lines (federation), and of course our little British-Irish war....so you see a unified area with conflicts like in the times the French conquered the British, Robin Hood ruled the woods of Sherham, and Arthur was forming his roundtable....do we ever change nope, economic power will switch from the "West" to the "East" for the current century, and maybe after three generations the European Tigers will make their appearance on the big screen agaiin taking over with the Russian's maybe????? remember they were big once....

but on a serious note, i've forgot indeed the ; to replace by , i guess i got access to an english version today so i could rewrite that function properly

:O)Bruintje
0

Commented:
those where the woods of Sherwood....anyway changed the function should be like this

of course Vind = Find how easy :O)
0

Commented:
and finally i got that running for the complete sheet

:O)Bruintje
0

Commented:
Hey bruintje and stochastic, here's a nice one:

1. Enter an excel formula in a cell and keep the cell active.
The formula can be written in dutch, swedish, english, whatever, depending on the currently used version of Excel...

=SUMMA(A1:A10;C1:C10)*1,25

2. Start Visual Basic Editor (Alt+F11)

3. Enter this in the immediate window:
? ActiveCell.Formula

See? This is an easy way to get the english formula. It converts function names as well as list separators (";" to ",") and number formats (1,25 to 1.25)

/Ture
0

Commented:
thanks for that, since i got some trouble in working with the dutch evrsions of formulas as demonstrated :O)
0

Commented:
...and of course, it works the other way around also. If you know the english formula, you can convert it to your language:

1. Select an empty cell

2. Press Alt+F11

3. Enter this in the immediate window:
ActiveCell.Formula = "=SUM(A1:A3,E1:E3)*1.23"

Now return to Excel and check out the formula in the active cell. The formula is displayed using the language of the Excel version you are using and the list separator and number formats set in your control panel's regional settings.

/Ture
0

Commented:
stochastic,

Any news on this? I know you haven't been away for very long, but I'm curious...

/Ture
0

Author Commented:
Ture, Bruintje,

Thanks for all your help. I'm closing this question by accepting Ture's latest answer. But I would like to thank Bruintje too, so I'm posting another one with points for him.

The best thing about EE is very often I get more value out of the answers than I ask for!

Thanks again.
stochastic
0

Author Commented:
Ture, Bruintje,

Thanks for all your help. I'm closing this question by accepting Ture's latest answer. But I would like to thank Bruintje too, so I'm posting another one with points for him.

The best thing about EE is very often I get more value out of the answers than I ask for!

Thanks again.
stochastic
0

Commented:
stochastic,

Thanks for the points and thank you for giving Bruintje some points too for his idea to use CELL("address") which was better than my original suggestion with a custom function.

/Ture
0

Commented:
Ture i appreciate your excellence at Excel stuff, it makes me taking a hard look at things, CELL("Address") was pretty unknown to me before this question :O)
0

Commented:
:o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.