Finding column Letter

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi.

Is there a way to return the column letter of a given
cell?

For instance, the following example code will return the
address of certain cell. In this case, it will
return "$I$1". I need, however, to return the column
letter of that cell. In this case, I would need it to
return "I".

Any ideas?

Thanks,
Mike.

Sub deleteme()
Dim MonthRange As Range
Dim i As Integer
Dim lastweek As Integer
Dim temp As String
Dim MonthCheck As Date
MonthCheck = #2/1/2004#
Set MonthRange = Worksheets("Blank 1").Range("e1:t1")
i = 1
Do While MonthRange(i) <= MonthCheck
i = i + 1
Loop
lastweek = i - 1
temp = MonthRange(lastweek).Address
End Sub
 
You can use this Mike

Function ColumnLetter(ColNumber) As String
'Chip Pearson
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber > 26))
End Function


Sub test()
MsgBox ColumnLetter(ActiveCell.Column)
End Sub
 
Mike,

From the Google archives (compliments of Jim Rech)

Left(ActiveCell.Address(, False), IIf(ActiveCell.Column > 26, 2, 1))

John
 
Back
Top