Page 1 of 3 123 LastLast
Results 1 to 10 of 30

vba code to convert number into a column letter

This is a discussion on vba code to convert number into a column letter within the Excel Questions forums, part of the Question Forums category; I am using some code which currently converts a number into the corrisponding column letter. eg 1 = A, 2 ...

  1. #1
    New Member
    Join Date
    Mar 2012
    Posts
    3

    Default vba code to convert number into a column letter

    I am using some code which currently converts a number into the corrisponding column letter. eg 1 = A, 2 = B, 27 = AA, 56 = BD etc.

    Here is the code:

    If mycolumn > 26 Then
    Mcl = Chr(Int((mycolumn - 1) / 26) + 64) & Chr(Int((mycolumn - 1) Mod 26) + 65)
    Else
    Mcl = Chr(mycolumn + 64)
    End If

    Where "mycolumn" = number & "Mcl" = letter.

    This code will only calculate correctly for numbers up to 702 as this is ZZ.
    How can i add onto this code to calculate for 3 letter column. eg AAA = 703, 4206 = EFT.

    I cant seem to get my head around it.

  2. #2
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    13,775

    Default Re: vba code to convert number into a column letter

    Welcome to the board.

    One way:

    Code:
    Function ColLtr(iCol As Long) As String
        If iCol > 0 And iCol <= Columns.Count Then
            ColLtr = Evaluate("substitute(address(1, " & iCol & ", 4), ""1"", """")")
        End If
    End Function
    Microsoft MVP - Excel

  3. #3
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,787

    Default Re: vba code to convert number into a column letter

    Why do you need to do this? You can work with column numbers directly in VBA.

    But here you go:
    Code:
     
    Function ColLtr(iCol As Long) As String
    If iCol > 0 And iCol <= Columns.Count Then ColLtr = Replace(Cells(1, iCol).Address(0, 0), 1, "")
    End Function
    Office 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    4,423

    Default Re: vba code to convert number into a column letter

    This would get you up to ZZZ:

    Code:
    Sub colLtr()
    Dim mycolumn
    mycolumn = 150
    If mycolumn > 52 Then
    Mcl = Chr(Int((mycolumn - 1) / 52) + 64) & Chr(Int((mycolumn - 27) / 26) + 64) & Chr(Int((mycolumn - 27) Mod 26) + 65)
    ElseIf mycolumn > 26 Then
    Mcl = Chr(Int((mycolumn - 1) / 26) + 64) & Chr(Int((mycolumn - 1) Mod 26) + 65)
    Else
    Mcl = Chr(mycolumn + 64)
    End If
    End Sub

    But as previously noted, you can work with column numbers by using the Cells index method.

  5. #5
    New Member
    Join Date
    Mar 2012
    Posts
    3

    Default Re: vba code to convert number into a column letter

    I am not sure on how to use the index method as i have not run functions before only subs. How would i be able to use that code to take a number in one cell and return the column letter in another?

    JLGWhiz - The code you wrote is on the right lines but does not work correctly. eg for number 703 should return AAA however it returns MZA. Also it should start with:

    If mycolumn > 702 Then

    Thanks for your help guys!

  6. #6
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,391

    Default Re: vba code to convert number into a column letter

    Here is the first hit I got on google:

    http://support.microsoft.com/kb/833402

    I wonder why they didn't test it properly before publishing it?
    Last edited by Colin Legg; Mar 22nd, 2012 at 08:03 PM.

  7. #7
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    13,775

    Default Re: vba code to convert number into a column letter

    This would get you to column FXSHRXW (for Excel 2050):

    Code:
    Function ColLtr(ByVal iCol As Long, Optional sCol As String = "") As String
        ' shg 2012
        If iCol = 0 Then
            ColLtr = sCol
        Else
            sCol = Chr(65 + (iCol - 1) Mod 26) & sCol
            iCol = (iCol - 1) \ 26
            ColLtr = ColLtr(iCol, sCol)
        End If
    End Function
    E.g., =ColLtr(2147483647)
    Microsoft MVP - Excel

  8. #8
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    4,423

    Default Re: vba code to convert number into a column letter

    Quote Originally Posted by AlphaRisk View Post
    I am not sure on how to use the index method as i have not run functions before only subs. How would i be able to use that code to take a number in one cell and return the column letter in another?

    JLGWhiz - The code you wrote is on the right lines but does not work correctly. eg for number 703 should return AAA however it returns MZA. Also it should start with:

    If mycolumn > 702 Then

    Thanks for your help guys!
    Yes, I noticed that my math was in error and am working on that. I probably have the correct code tucked away somewhere but had not been active in the forums for a while and forgot where I kept stuff. If I get it, I will post it. There is a simple way to do it but my memory lapse is preventing me from producing it.

  9. #9
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    13,775

    Default Re: vba code to convert number into a column letter

    Actually ...

    Code:
    Function ColLtr(ByVal iCol As Long) As String
        ' shg 2012
        If iCol Then ColLtr = ColLtr((iCol - 1) \ 26) & Chr(65 + (iCol - 1) Mod 26)
    End Function
    Microsoft MVP - Excel

  10. #10
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    4,423

    Default Re: vba code to convert number into a column letter

    Quote Originally Posted by AlphaRisk View Post
    I am not sure on how to use the index method as i have not run functions before only subs. How would i be able to use that code to take a number in one cell and return the column letter in another?

    JLGWhiz - The code you wrote is on the right lines but does not work correctly. eg for number 703 should return AAA however it returns MZA. Also it should start with:

    If mycolumn > 702 Then

    Thanks for your help guys!

    OK, I came to my senses. Here is the code:

    Code:
    Sub colLtr()
    Dim mycolumn
    mycolumn = 1450
    Mcl = Left(Cells(1, mycolumn).Address(1, 0), InStr(1, Cells(1, mycolumn).Address(1, 0), "$") - 1)
    MsgBox Mcl
    End Sub

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com