Current cell location in a cell
Hi..
I need a function(s) that can indicate the current cell column and row in two different cells.
e.g.
Let's say in Cell A1 should indicate the column of current selected cell and B1 indicates the row of current selected cell.
A1 would read - 7 and B1 would read X if the current cell is at X7.
I do not know VBA. If you give VBA solution, please give me link on how to activate the codes.
Thanks in advance.
I don't know of a way to do it with a formula, but there may be one.
If you don't mind VBA, here's a macro that will do that:
How to use the macro:Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim strCell As String, i As Long, R As Long, C As String Application.ScreenUpdating = False Application.EnableEvents = False strCell = Target.Address For i = 1 To Len(strCell) Select Case Mid(strCell, i, 1) Case "$" 'do nothing Case "A" To "ZZ" C = C & Mid(strCell, i, 1) Case 0 To 9 R = Mid(strCell, i, 100) Exit For End Select Next i Range("A1") = R Range("B1") = C Application.ScreenUpdating = True Application.EnableEvents = True End Sub
1. Open up your workbook
2. Right-click on the sheet tab where you want this to happen, select VIEW CODE
3. Copy and Paste in your code (given above)
4. Get out of VBA (Press Alt+Q)
5. Save your sheet
It's now installed and A1 and B1 will change values as you move the activecell.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hi JBeaucaire
This would solve my problem.
I did excately as per your instructions. It wokred for a while and now it is not working.
I re-saved, enabled macro but still not working.
Am I missing anything?
Thanks.
Click GO ADVANCED and use the paperclip icon to post up your workbook.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
JBeaucaire
Here is attached sample with copied code and not working in my laptop.
I have one more question. How do we write (copy/paste) multiple macro codes?
Thanks a lot.
The security on your laptop must be set too high. This worked for me on my desktop and my laptop.
The macro itself is pretty basic, so that's not the issue. It will be something to do with your setups. Any chance the macros are disabled by other sheets/stuff you're working on?
On the Control Toolbox toolbar, click on the Design Mode/Normal Mode button to toggle macros off and on, see if that jiggles it awake it stops working on you.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Oh yeah, the macro itself turns off macros when it updates the sheet, something I do out of habit. I tested it without that there and it seems to be OK. So you can take out the two "EnableEvents" lines of code, see if that helps.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
JBeaucaire
Seems I will not be able to activate macros. My laptop setting are as per my employers. I am preparing timesheet module to be used by my team members. Even-if I change the settings and make it work in my laptop - it will work for all team mates.
Is there any function to achieve this. Else I will post the objectvie - we might solve thru some other method.
Thank and regards..
I would imagine you'll need to examine WHY you needed pieces of the active cell in cells A1 an B1 and try to resolve THAT issue another way.
Care to share?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Re: Current cell location in a cell
JBeaucaire
Yes. Let's solve it differenty.
I am preparing fool-proof timesheet. Please refer attached file. The blue cells indicates weekending sunday's date. Since there can be different dates there, I want to show dates of the week in red cells depeding on where the current cell is in yello area.
Clarification / e.g.:
1. If current cell is in row 14 (7/5) and in yellow, the dates in RED would be 6/29 thru 7/5.
2. If current cell is in row 15 (7/19) and in yellow, the dates in RED would be 7/13 thru 7/19.
3. If current cell is outside yellow - the RED would be blank.
Simpler solution would be to show entire month's calender as this timesheet is specific to a month.
Also my plan is to implement conditional formating, prevening user to see formulas, change data where he/she is not supposed to - even if by an accident.
If you noticed, the timesheet month and update dates are controlled by the file name. I will enter the PO and PO Number thru conditional combo box (where you already helped me).
Pls suggest as you see fit. Not sure if can continue discussing other points in this thread as per forum rules.
Thanks and regards..
OK, so you can't do #1, #2 or #3 without VBA, at least not that I know of. So you have nailed it, an issue that really requires VBA. Once VBA is active, you wouldn't have needed the A1,B1 thing anyway, the same macro could have simply put the dates in the red cells.
Perhaps you should start a new thread specific to this topic:
"Change cell values based on Active Cell Address in Excel 2007 with no VBA"
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hello JBeaucaire
i was wondering if i could use the same macro as a part of a bigger one. How should it be in order to fit in there? i mean i guess that these
shouldn't exist or they should be written otherwise...but how??Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Thank you
As per the Forum Rules you should start your own thread, include sample workbooks and an explanation of your full actual question. If you find this thread of particular interest, include a link to it, but it might just confuse the situation.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks