Results 1 to 7 of 7

Thread: Converting a Column Number to a Column Letter

  1. #1
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14

    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
    xl2007 - Windows 7
    xl hates the 255 number

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Rasm View Post
    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. You can shorten your typing by an entire character if you do it this way ...

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

  3. #3
    Junior Member Preeti Verma's Avatar
    Join Date
    May 2012
    Posts
    5
    Rep Power
    0
    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
    Last edited by Admin; 12-15-2012 at 09:16 AM.

  4. #4
    Junior Member
    Join Date
    Dec 2012
    Posts
    12
    Rep Power
    0
    When testing the code I have got an error

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

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by KingTamo View Post
    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)

  6. #6
    Junior Member
    Join Date
    Dec 2012
    Posts
    12
    Rep Power
    0
    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

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by KingTamo View Post
    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.

Similar Threads

  1. Replies: 10
    Last Post: 05-23-2013, 12:30 PM
  2. Replies: 7
    Last Post: 04-21-2013, 07:50 PM
  3. Replies: 11
    Last Post: 10-07-2012, 12:05 AM
  4. Replies: 1
    Last Post: 08-07-2012, 11:04 PM
  5. Replies: 3
    Last Post: 08-05-2012, 09:16 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •