Determining cursor position in a named range

  • Thread starter Thread starter John
  • Start date Start date
J

John

what macro code do I use to find the row and column position of the cursor in
a named range table on a worksheet?

If the cursor is not exatactly in the named range, I'd like to determine the
row if that is win the table, the column if that is in the table, or if both
are out of the table range.

I appreciate your help, -John
 
what macro code do I use to find the row and column position of the cursor in
a named range table on a worksheet?

If the cursor is not exatactly in the named range, I'd like to determine the
row if that is win the table, the column if that is in the table, or if both
are out of the table range.

I appreciate your help, -John
Click to expand...

John,

You can use the Row and Column property of a Range object. For
example, the following code will print the results to the Immediate
Window.

'This is for the ActiveCell
Debug.Print ActiveCell.Row
Debug.Print ActiveCell.Column

'This is for a specified range location
Debug.Print Range("AA5").Row
Debug.Print Range("AA5").Column

Matt
 
Your question (as to what you want reported back to you) is not very clear
to me. Does this do what you want...

Sub WhereIsActiveCell()
Dim Status As String
If Intersect(Range("RngName"), ActiveCell.EntireRow) Is Nothing Then
Status = "ActiveCell's Row is not within the Named Range."
Else
Status = "The ActiveCell's Row is located within the Named Range."
End If
If Intersect(Range("RngName"), ActiveCell.EntireColumn) Is Nothing Then
Status = Status & vbLf & vbLf & _
"ActiveCell's Column is not within the Named Range."
Else
Status = Status & vbLf & vbLf & _
"The ActiveCell's Column is located within the Named Range."
End If
MsgBox Status
End Sub
 
Rick, I apologized for the lack of clarity. Sometimes it's hard to clearly
articulate a question. That said, I messed w/ your code and found the
following gives me the row in the table where the cursor is, or a msg if it
is outside the table range.
On Error Resume Next
CursorRow = Intersect(Range(Table), ActiveCell).Row
FirstTableRow = Intersect(Range(Table), Range(Table)(1, 1)).Row
If Err <> 0 Then
MsgBox "Cursor is not positioned inside table."
Exit Sub
End If
On Error GoTo 0
CursorTableRow = CursorRow - FirstTableRow + 1
This seems like a lot of code to find CursorTableRow, but it works.

Thx so much for your patience and help, John
 
Back
Top