Function to convert column number to letter?

This function returns the column letter for a given column number.

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

testing code for column 100

Sub Test()
    MsgBox Col_Letter(100)
End Sub

Something that works for me is:

Cells(Row,Column).Address 

This will return the $AE$1 format reference for you.

If you'd rather not use a range object:

Function ColumnLetter(ColumnNumber As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String

    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function

• For example: MsgBox Columns( 9347 ).Address returns .

---

To return ONLY the column letter(s): Split((Columns(Column Index).Address(,0)),":")(0)

• For example: MsgBox Split((Columns( 2734 ).Address(,0)),":")(0) returns .

---

  

---