How do I get the column of a cell?

[debartsa]

Hi Everybody,

I'm working with the Excel 97 Object Model. Does anyone know of a way to
find the "column" value of a current cell?

For example let's say I have a reference to the cell at Address "B5" (I
won't know the address in advance at runtime). Is there a call I can make
to return just the column ordinal i.e. "B"

The closest I get is:
cell.Columns.Address(RowAbsolute:=False, ColumnAbsolute:=False)

which returns "B5"


Thanks for any help
Sam

 

[Carim]

Hi Sam,

Why don't you just add Left(x,1)
Left(Target.Columns.Address(, ColumnAbsolute:=False), 1)

HTH
Cheers
Carim

 

[debartsa]

Thanks Carim,

I'll give it a try!

 

[Guest]

I think Carim's is good if you only plan to need a single-letter column.
The following will work for any column:

Private Sub TestCol()

Dim sAddress As String
Dim sCol As String
sAddress = Cells(1, ActiveCell.Column).Address(False, False)
sCol = Left(sAddress, Len(sAddress) - 1)
MsgBox sCol

End Sub

 

[Tom Ogilvy]

Range("B5").Select
? Left(ActiveCell.Columns.Address(, ColumnAbsolute:=False), 1)
B
Range("BB5").Select
? Left(ActiveCell.Columns.Address(, ColumnAbsolute:=False), 1)
B

 

[Nick Hebb]

Here's one possible solution using mod arithematic:

Sub GetSelectedColumn()

Dim iCol As Integer
Dim sCol As String

iCol = ActiveCell.Column

If iCol > 26 Then
sCol = Chr(Int(iCol / 26) + 64) & Chr((iCol Mod 26) + 64)
Else
sCol = Chr(iCol + 64)
End If
MsgBox sCol

End Sub

Notes:
- Excel's columns go from A = 1 to IV = 256
- The Chr() function returns the character for a given ASCII value,
where Chr(65) = 'A', Chr(66) = 'B', etc.
- Int() truncates a division to the integer portion and Mod gives the
remainder of the dision operation. These two values are then used to
get the 1st and 2nd column identifiers.

--Nick

 

[debartsa]

Thanks to all the others who posted alternate solutions!