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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Mike" wrote in message ...
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

Mike,

From the Google archives (compliments of Jim Rech)

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

John


"Mike" wrote in message
...
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