?
Solved

current cell position in Excel

Posted on 2001-09-04
22
Medium Priority
?
31,176 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:stochastic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 5
  • +1
22 Comments
 
LVL 22

Expert Comment

by:ture
ID: 6452668
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
Karlstad, Sweden
0
 
LVL 8

Author Comment

by:stochastic
ID: 6452785
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
 
LVL 22

Expert Comment

by:ture
ID: 6452799
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Expert Comment

by:skillsw
ID: 6452916
Hi
Write this code in VBA

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

Rgds

Harsha
0
 
LVL 8

Author Comment

by:stochastic
ID: 6453196
Harsha,

Thanks for your help, but I'm afraid I must reject your answer because both the ideas you proposed, namely,
a. addressing the selectionchange event
b. using activecell.row property
had been already covered. Your suggestion does not give me anything that I and Ture hadn't already explored.

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
 
LVL 44

Expert Comment

by:bruintje
ID: 6472335
maybe =CELL("Address") will help

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
 
LVL 44

Expert Comment

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

=RECHTS(RECHTS(CEL("Address");VIND.SPEC("$";CEL("Address");3));LENGTE(RECHTS(CEL("Address");VIND.SPEC("$";CEL("Address");3)))-1)

should be something like

=RIGHT(RIGHT(CELL("Address");instring("$";CELL("Address");3));LENGTH(RIGHT(CELL("Address");INSTRING("$";CELL("Address");3)))-1)

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
 
LVL 44

Expert Comment

by:bruintje
ID: 6472431
btw the instring should be SEARCH i guess so it would be like

=RIGHT(RIGHT(CELL("Address");SEARCH("$";CELL("Address");3));LENGTH(RIGHT(CELL("Address");SEARCH("$";CELL("Address");3)))-1)
0
 
LVL 8

Author Comment

by:stochastic
ID: 6473151
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6473173
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6473319
those where the woods of Sherwood....anyway changed the function should be like this

=RIGHT(RIGHT(CELL("Address"),FIND("$",CELL("Address"),3)),LEN(RIGHT(CELL("Address"),FIND("$",CELL("Address"),3)))-1)

of course Vind = Find how easy :O)
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6473511
and finally i got that running for the complete sheet

=RIGHT(CELL("Address"),LEN(CELL("Address"))-FIND("$",CELL("Address"),3))

:O)Bruintje
0
 
LVL 22

Expert Comment

by:ture
ID: 6473603
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6473615
thanks for that, since i got some trouble in working with the dutch evrsions of formulas as demonstrated :O)
0
 
LVL 22

Expert Comment

by:ture
ID: 6473633
...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
 
LVL 22

Expert Comment

by:ture
ID: 6476850
stochastic,

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

/Ture
0
 
LVL 22

Accepted Solution

by:
ture earned 400 total points
ID: 6476881
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.

=MID(CELL("address"),FIND("$",CELL("address"),2)+1,9)

/Ture
0
 
LVL 8

Author Comment

by:stochastic
ID: 6477307
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
 
LVL 8

Author Comment

by:stochastic
ID: 6477308
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
 
LVL 22

Expert Comment

by:ture
ID: 6478687
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
 
LVL 44

Expert Comment

by:bruintje
ID: 6479540
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
 
LVL 22

Expert Comment

by:ture
ID: 6479590
:o)
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question