Converting a Column Number to a Column Letter

[Rasm]

Astr = Split(Cells(1, Along).Address, "$")(1)

Where
Along = Column number to be converted to letter

Astr = column letter returned

[Rick Rothstein]

Astr = Split(Cells(1, Along).Address, "$")(1)

Where
Along = Column number to be converted to letter
Astr = column letter returned
That code line looks awfully long to me.:rolleyes: You can shorten your typing by an entire character if you do it this way:laugh: ...


Astr = Split(Columns(Along).Address, "$")(2)

[Preeti Verma]

Option Explicit

'
'UDF to obtain column field for any cell(i,j)
'

Function ColFld(ByVal num1 As Long, ByVal num2 As Long) As String
Dim str As String, i As Long
str = Replace(Cells(num1, num2).Address, "$", "")
For i = 0 To 9
str = Replace(str, CStr(i), "")
Next i
ColFld = str
End Function
'
'UDF to obtain cell address field for any cell(i,j)
'

Function CellFld(ByVal v1 As Long, ByVal v2 As Long) As String
Dim st As String, j As Long
For j = 0 To 9
st = Replace(Cells(v1, v2).Address, "$", "")
Next j
CellFld= st
End Function
'
'UDF to obtain row field for any cell(i,j)
'


Function RowFld(ByVal num1 As Long, ByVal num2 As Long) As String
Dim str As String, i As Long
str = Replace(Cells(num1, num2).Address, "$", "")
For i = 65 To 90
str = Replace(str, Chr(i), "")
Next i
RowFld = str
End Function

[KingTamo]

When testing the code I have got an error


Sub Test()
Astr = Split(Cells(1, Along).Address, "$")
MsgBox Astr
End Sub

[Rick Rothstein]

When testing the code I have got an error


Sub Test()
Astr = Split(Cells(1, Along).Address, "$")
MsgBox Astr
End Sub
Split returns an array of values, not a single value, so you have to specify which member of the array you want...

MsgBox Astr(1)

[KingTamo]

Thanks for reply
But I have an error too when testing the code

Sub Test()
Astr = Split(Cells(1, Along).Address, "$")
MsgBox Astr(1)
End Sub

[Rick Rothstein]

Thanks for reply
But I have an error too when testing the code

Sub Test()
Astr = Split(Cells(1, Along).Address, "$")
MsgBox Astr(1)
End Sub
Did you notice the explanation for "Along" from the first message (from where the code snippet came)? Namely...

"Where
Along = Column number to be converted to letter"

The 1 inside the Cells call is the row and Along is a variable that should be assigned (or fully replaced by) a column letter.