When

  • Thread starter Thread starter kirkm
  • Start date Start date
K

kirkm

When you pass Target.Address as a parameter e.g $I$3466
is there a system command to get the row and column from that?
(I know it's dead easy to DIY but just wondering).

Thanks - Kirk
 
Hi Kirk,

Try:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Column: " & Split(Target.Address, "$")(1) & _
vbCrLf & "Row: " & Split(Target.Address, "$")(2)
End Sub

or:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Column: " & Target.Column & vbCrLf & "Row: " & Target.Row
End Sub

--
Cheers
macropod
[Microsoft MVP - Word]


in message news:[email protected]...
 
Your question is not entirely clear. What do you mean when you say "pass
Target.Address"... do you mean pass that into another subroutine or
function? Or did you mean pass the String value $I$3466 into another
subroutine or function? Or did you mean as macropod indicated... working
with the Target argument of an event procedure within the event procedure.
Either of the first two can be done by using the address as an argument to
the Range object and then querying it for the row and column. For example,
assuming Arg is the name of the argument in the subroutine or function that
you are passing either Target.Address or "$I$3466" into...

MsgBox "Column: " & Range(Arg).Column & vbCrLf & "Row: " & Range(Arg).Row
 
On Mon, 25 May 2009 17:03:32 +1200, kirkm wrote:

Thanks very much everyone. Yes, it wasn't very well put,
the question. It was after being passed to a function
where I wanted the column and row values, without the $.

I changed to sending Target.Row in instead of Target.Address,
but Rick has the answer I was seeking...

line = range("$I$3266").Row (or .Column)

Neater and shorter than what I had, or Split (nice command!)

Cheers - Kirk
 
Back
Top