Solved

current cell position in Excel

Posted on 2001-09-04
22
31,130 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
SendBlaster Pro 4 - Bulk Email Sending Software

SendBlaster 4 Pro - Best Bulk Emailing Sending Software
Automatic Subscribe / Unsubscribe Processing
Great for Newsletters & Mass Mailings
Optional HTML & Text Composition
Integration with Google Features
Built in Spam Score Checking
Free Professional Templates - Feature Packed!

 

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 100 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 Advanced Training for Admins

Special Offer:  Buy 1 course, get 2nd free!  Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

732 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