Converting a Column Number to a Column Letter

Code:

---

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

---

Where
Along = Column number to be converted to letter

Astr = column letter returned

Quote:

---

Originally Posted by Rasm

Code:

---

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: ...

Code:

---

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

---

Option Explicit

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

Code:

---

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)
'

Code:

---

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)
'

Code:

---

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

---

When testing the code I have got an error

Code:

---

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

---

Quote:

---

Originally Posted by KingTamo

When testing the code I have got an error

Code:

---

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)

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

Code:

---

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

---

Quote:

---

Originally Posted by KingTamo

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

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.