Solved

current cell position in Excel

Posted on 2001-09-04
22
31,094 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
  • 8
  • 8
  • 5
  • +1
22 Comments
 
LVL 22

Expert Comment

by:ture
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Expert Comment

by:skillsw
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 44

Expert Comment

by:bruintje
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
...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
Comment Utility
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 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
:o)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now