Convert Cell Address to Row/Column

[medberg]

My issue is that I need to convert a Cell address such as 'A1' into just A and 1, or even better the A into a 1 as well (It's column number). Any help would be greatly appreciated. Thanks!

 

[medberg]

More specifically, here is the code that gets me an 'A1'.

Sheets("Log").ListObjects(1).Range.Cells(1).Address(0, 0)

Anything to seperate this into rows and columns would be awesome. Thanks

 

[Ruddles]

I think it's:-

MyRow = Range(Sheets("Log").ListObjects(1).Range.Cells(1).Address(0, 0)).Row
MyColumn = Range(Sheets("Log").ListObjects(1).Range.Cells(1).Address(0, 0)).Column

On second thoughts, isn't it just:-

MyRow = Sheets("Log").ListObjects(1).Row
MyColumn = Sheets("Log").ListObjects(1).Column

Try them both.

 

[shg]

This is ugly ...

    Dim avs As Variant
 
    avs = Split(Mid(Worksheets("Log").ListObjects(1).Range.Cells(1).Address(ReferenceStyle:=xlR1C1), 2), "C")

    MsgBox avs(0) & " " & avs(1)

 

[Scott Huish]

Sub test()
Dim address1 As String, myColumn As Long, myrow As Long
address1 = Sheets("Log").ListObjects(1).Range.Cells(1).Address(0, 0)
myColumn = Range(address1).Column
myrow = Range(address1).Row
MsgBox "Column is " & myColumn
MsgBox "Row is " & myrow
End Sub

 

[medberg]

Ruddles, I ended up using your first solution and it worked great. I didn't test the second because my code had to be explicitly laid out like it was in the first. I'm pretty new to this, so thank you everyone for your help.

 

[bkelly]

followup question

I read a cell and get "=SHEET1!C5", then extract the "C5." How do I converte the C5 to 3,5.

I suppose I could select cell C5 then ask for the row and column but am hoping for a more direct solution.

 

[Ruddles]

If you genuinely have the string in a VBA variable - let's say it's called sAddr - which represents a cell address, then to get the row and column you'd go:-

MsgBox Range(sAddr).Row
MsgBox Range(sAddr).Column

I've got a feeling you're extracting the address of a cell and then converting it to column and row, whereas... perhaps you can get the .Row and .Column of the original cell?

Maybe show us the code and tell us what you're trying to achieve?

 

[bkelly]

I have a workbook with a cells that has, for example: "=Sheet6!C5" and need to write code that reads the contents of sheet 6 cell C5. If that cell contains an "=" then I do it again until I get to the original data. Then I will grab some data off that sheet in other cells.

I have not written the code for this function yet. All the code I have written uses the row,column format. So I want to get from the C5 to 5,3.

I am working on a telemetry system and the telemetry maps are rather convoluted and and I need to chase items down through a page or three to find what I'm looking for.

I think that Range().row will work. I will give it a go.

 

[bkelly]

Oops, very slow computer and link, double post.