Get the column-name/row-number from a named cell

[Willem!]

Hi!

Is there a way to find the colum-name and the row-number from a named cell?

I know that I can get the complete address using:

Sheet1.Range("some_named_cell").Address

Answer (e.g.): $A$1

I want to seperately get 'A' and '1' (in VBA).

Is this possible?

TIA,

Willem!

 

[Leith Ross]

Hello Willem!,

The Range object has to properties just for that purpose: .Column and .Row

  Dim C As Variant
  Dim R As Long

  With Sheets(1).Range("some_named_cell")
    C = .Columns(.Column)
    R = .Row
  End With

This code will return the column as a letter and the row as a number for a single cell or the first cell on a range.

Sincerely,
Leith Ross

 

[Leith Ross]

Hello Willem!,

Ignore my previous post. This method is better. It works with Excel 2000 and up.

  Dim C As Variant
  Dim R As Long
  Dim RC As Variant

    RC = Split(Range("A1").Address(True, False), "$")
    R = RC(1)
    C = RC(0)

Sincerely,
Leith Ross

 

[Willem!]

Hi Leith,

Thank you for your quick reply. When I use your code though, I get an odd small number (double) for the column-name instead of a letter.

When I change your code to:

Dim C As Variant
Dim R As Long
 
With Sheets(1).Range("some_named_cell")
C = .Column
R = .Row
End With

I get a number for the column-name, which solves my problem also (I can use this number also).

So thank you very much!

 

[Willem!]

Ah, well, even better then.

I will use the code in your second reply, works like a charm and I agree that it's better (without the with-statement).