This algorithm converts column numbers (
1-x) to Excel column names. It works for any number and does not have a limit.
'--------------------------------------------------
' Columns can be any number from 1 to x
' ASCII A = 65 so since we're starting with 1
' chr(intCol + 64 ) = A
'--------------------------------------------------
Function ExcelCol(ByVal intCol As Long) As String
'Subtract 1 for because intCol = 1 = 'A'
If intCol <= 26 Then
ExcelCol = Chr(intCol + 64)
Else
intRemainder = intCol Mod 26
intMod = intCol \ 26
If intRemainder = 0 Then
ExcelCol = ExcelCol(intMod - 1) & "Z"
Else
ExcelCol = ExcelCol(intMod) & Chr(intRemainder + 64)
End If
End If
End Function
**** NON Recursive Version ***
Function ExcelColNonRec(ByVal intCol As Long) As String
While intCol > 26
intRemainder = intCol Mod 26
intCol = intCol \ 26
If intRemainder = 0 Then
ExcelColNonRec = ExcelColNonRec & "Z"
intCol = intCol - 1
Else
ExcelColNonRec = ExcelColNonRec & Chr(intRemainder + 64)
End If
Wend
Col2 = Col2 + Chr(intCol + 64)
End Function
