how to get row() & Col() of curent activ cell

January 13, 2011 at 20:56:56
Specs: Windows 7
As I have made my own Rotary calender in Excel 2003 for my Rotary year 1st July 2011 To 30th June 2012, I want that when i click mouse in any cell in the calender I want all the appointments listed for that date in another worksheet will display to know me what r the appointments i have made for that day so want the row() & Col() of current active cell so that i retrieve date stored in that cell to display the appointments for that day
Thanks who will suggest the right way
My email xxxxx.xxxx@xxxxx.xxx

edited by moderator: email address deleted


See More: how to get row() & Col() of curent activ cell

Report •

#1
January 13, 2011 at 21:14:40
Please do not post your email address in a public forum.

You don't want to be subjected to a ton of spam and we don't want to be known as a forum where email addresses can be harvested.

I have deleted your address from your post.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 13, 2011 at 21:39:16
This code will return the Row and Column numbers of the cell you select. Click on the sheet tab for the sheet you will be selecting a cell in, choose View Code, and paste the code into the pane that opens.

I'm not sure what you want to do with that information, so the code will do 2 things:

1 - Place a formula in Sheet2!A1 to retrieve the contents of that cell based on the Row and Column numbers.

2 - Place the contents of the selected cell directly into Sheet2!A2 without using a formula.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Build formula for Sheet2!A1 using Row & Column numbers
'e.g. =Sheet1!$A$4
  Sheets(2).Range("A1").FormulaR1C1 = _
    "=" & Sheets(1).Name & "!R" & Target.Row & "C" & Target.Column
    
'Place contents of selected cell directly in Sheet2!A2
  Sheets(2).Range("A2") = Sheets(1).Range(Target.Address)

End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question