vbaexcel

Get the row value and the cell value from Selection.Address

Code for searching a spread sheet to find a specific string and then select it.

For i = 1 To 20

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

  '' get the cell address
  
  CellAddr = Selection.Address(False, False, xlR1C1) 

Next

Selection.Address returns something along the lines of R[100]C.

Is there a way I can split that result into 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.

Solution 
  • 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