PDA

View Full Version : [SOLVED] Get Column Letters (Excel 2010)



khalid79m
01-23-2013, 06:18 AM
Function xl_Col(ByRef Col_No) As String
'returns Excel column name from value
If Col_No < 1 Or Col_No > 16384 Then Exit Function
If Col_No < 27 Then 'Single letter
xl_Col = Chr(Col_No + 64)
End If
If Col_No >= 27 And Col_No < 703 Then 'Double Letter
xl_Col = Chr(Int((Col_No - 1) / 26) + 64) & Chr(((Col_No - 1) Mod 26) + 1 + 64)
End If
If Col_No >= 703 And Col_No < 16385 Then 'Three Letters
'???????????????
End If
End Function


I have had this code for years and it works fine, now we have switched over to 2010 and we are using columns beyond "AAA" (Three letters). I cant seem to get the code right can anyone help ??:banghead:

JKwan
01-23-2013, 07:00 AM
Try this function


Public Function ColumnLetter(colNum As Long) As String
'This function will return a Column Number to a Column Letter
' =ColumnLetter(10) This will return "J"
Dim i, x As Long
For i = Int(Log(CDbl(25 * (CDbl(colNum) + 1))) / Log(26)) - 1 To 0 Step -1
x = (26 ^ (i + 1) - 1) / 25 - 1
If colNum > x Then
ColumnLetter = ColumnLetter & Chr(((colNum - x - 1) \ 26 ^ i) Mod 26 + 65)
End If
Next i
End Function

patel
01-23-2013, 07:40 AM
Function xl_Col(ByRef Col_No) As String
'returns Excel column name from value
If Col_No < 1 Or Col_No > 16384 Then Exit Function
If Col_No < 27 Then 'Single letter
xl_Col = Chr(Col_No + 64)
End If
If Col_No >= 27 And Col_No < 703 Then 'Double Letter
xl_Col = Chr(Int((Col_No - 1) / 26) + 64) & Chr(((Col_No - 1) Mod 26) + 1 + 64)
End If
If Col_No >= 703 And Col_No < 16385 Then 'Three Letters
xl_Col = Chr(Int((Col_No - 1) / 702) + 64) & Chr(Int(((Col_No - 1) Mod 702) / 26) + 1 + 64) & Chr(((Col_No - 1) Mod 26) + 1 + 64)
End If
End Function

snb
01-23-2013, 08:19 AM
Sub M_snb()
For j = 0 To 3
MsgBox Split(Columns(Application.Min(26 ^ j, Columns.Count)).Address(0, 0), ":")(0)
Next
End Sub

sub M_snb_001()
msgbox F_column_snb(45)
end sub

Function F_column_snb(y)
F_column_snb=split(columns(y).address(0,0),":")(0)
End Function

Kenneth Hobs
01-23-2013, 05:08 PM
Function ColumnLetter(ColumnNum As Long) As String
ColumnLetter = Replace(Cells(1, ColumnNum).Address(0, 0), 1, "")
End Function

p45cal
01-23-2013, 06:59 PM
or


Function ColumnLetter(ColNum)
ColumnLetter = Split(Cells(, ColNum).Address, "$")(1)
End Function

Teeroy
01-25-2013, 02:02 AM
or similarly


Function ColumnLetter(ColNum)
ColumnLetter = Split(Columns(colnum).Address, "$")(2)
End Function