excel vba getting the row,cell value from selection.address

Question

For i = 1 To 20

  '' select the cell in question
  Cells.Find(...).Select

  '' get the cell address

  CellAddr = Selection.Address(False, False, xlR1C1) 



Next

The above is my code for searching a spread sheet to find a specific string and then select it. I'd like to get the address of the cell using Selection.Address which, in this case, returns something along the lines of R[100]C. Is there a way I can split that result in to row and column values so I can manipulate them in code? I'd like, for example to add 14 rows to the selected cells row value. I believe CellAddr will be a Range object so it may work I'm just fuzzy on the implementation.

Thanks!

score 16 · Answer 1 · answered Oct 01 '13 at 01:25

16

Is this what you are looking for ?

Sub getRowCol()

    Range("A1").Select ' example

    Dim col, row
    col = Split(Selection.Address, "$")(1)
    row = Split(Selection.Address, "$")(2)

    MsgBox "Column is : " & col
    MsgBox "Row is : " & row

End Sub

answered Oct 01 '13 at 01:25

Santosh

12,175
4
41
72

1

Surely we should be able reference: Selection.Address.Row and Selection.Address.Column,right? It doesn't work for me, but I can't imagine the VBA gods would force us to use Split for such a trival task?? – Pete Alvin Sep 18 '20 at 18:37

Tim Williams · Accepted Answer · 2013-09-30T21:37:20.990

13

Dim f as Range

Set f=ActiveSheet.Cells.Find(...)

If Not f Is Nothing then
    msgbox "Row=" & f.Row & vbcrlf & "Column=" & f.Column
Else
    msgbox "value not found!"
End If

edited Sep 30 '13 at 21:37

answered Sep 30 '13 at 20:32

Tim Williams

154,628
8
97
125

excel vba getting the row,cell value from selection.address

2 Answers2

Linked