Menu
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!
927
For example: =ADDRESS(1,1) - returns the address of the first cell (i.e. the cell at the intersection of the first row and first column) as an absolute cell reference $A$1. =ADDRESS(1,1,4) - returns the address of the first cell as a relative cell reference A1.
For example, if you have an address, you can use the VBA Split function to get different parts of the address that are separated by a comma (which would be the delimiter in this case). SPLIT is an inbuilt string function in Excel VBA that you can use to split a text string based on the delimiter.
If the Excel VBA Range object you want to refer to is a single cell, the syntax is simply “Range(“Cell”)”. For example, if you want to make reference to a single cell, such as A1, type “Range(“A1″)”.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
