Possible Duplicate:
VBA function to convert column number to letter?
I need to display a msgbox that shows the last used column.
Right now the columns numeric value is displayed but can it be converted into its alpha character equivalent?
Ex. column 5 = column E.
Thanks
I managed to get 2 methods. One relies on the excel Cells, so it fails if you use an invalid column:
Function NumToLetter(q As Long) As String
Dim Add As String
'Ignore error so it fails on an invalid cell
On Error Resume Next
Add = Cells(1, q).Address
If Len(Add) Then
NumToLetter = Mid(Add, 2, InStr(2, Add, "$") - 2)
Else
NumToLetter = "Invalid Cell"
End If
End Function
the other uses base 26, and is only limited bu the maximum value for long:
Function Num2Letter(q As Long) As String
Dim r As Long
While q > 0
r = q Mod 26
If r = 0 Then r = 26 ' needed for column Z
Num2Letter = Chr(64 + r) & Num2Letter
q = Int(q / 26) + (r = 26) ' fix for column Z
Wend
End Function
to test these values with a messagebox:
msgbox "Column 2445 is " & NumToLetter(2445) & " or " Num2Letter(2445)
Column 2445 is CPA or CPA
msgbox "Column 42445 is " & NumToLetter(42445) & " or " Num2Letter(42445)
Column 42445 is Invalid Cell or BJTM
msgbox "Column -1 is " & NumToLetter(-1) & " or " Num2Letter(-1)
Column -1 is Invalid Cell or
This works for columns A through ZZ (as pointed out in the comments by Sean Cheshire):
Function ColLetter(ColNumber As Integer) As String
ColLetter = Left(Cells(1, ColNumber).Address(False, False), 1 - (ColNumber > 26))
End Function
To use: MsgBox ColLetter(oRangeObject.Column)
