How do I get the column of a cell?
 

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

--
Regards,
Tom Ogilvy

"debartsa" wrote in message
...
Thanks Carim,

I'll give it a try!


"Carim" wrote in message
oups.com...

Hi Sam,

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

HTH
Cheers
Carim

How do I get the column of a cell?
 

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