Function to convert column number to letter? Function to convert column number to letter? vba vba

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


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 = sEnd Function


Something that works for me is:

Cells(Row,Column).Address 

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


matomo