Convert Cell Address to Row/Column

medberg

New Member
Joined
Jul 29, 2011
Messages
30
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!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
More specifically, here is the code that gets me an 'A1'.
Code:
Sheets("Log").ListObjects(1).Range.Cells(1).Address(0, 0)
Anything to seperate this into rows and columns would be awesome. Thanks
 
Upvote 0
I think it's:-
Code:
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:-
Code:
MyRow = Sheets("Log").ListObjects(1).Row
MyColumn = Sheets("Log").ListObjects(1).Column
Try them both.
 
Upvote 0
This is ugly ...
Code:
    Dim avs As Variant
 
    avs = Split(Mid(Worksheets("Log").ListObjects(1).Range.Cells(1).Address(ReferenceStyle:=xlR1C1), 2), "C")

    MsgBox avs(0) & " " & avs(1)
 
Upvote 0
Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:-
Code:
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,593
Messages
6,179,791
Members
452,942
Latest member
VijayNewtoExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top