Results 1 to 7 of 7

Thread: Column Letter to Column Number conversion
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2005
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Column Letter to Column Number conversion

    Hi. Is there a VBA function which converts "H" to 8 and "AC" to 29, and in general, given any column letter label, returns the number of that column?

    I'm thinking
    Worksheets.("Sheet1").Columns("H").Index

    but of course there's no such property. Please help. Thank you.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default

    Phil

    Why do you need to convert the number to the letter?

    In code you can use the number.
    Code:
    Worksheets("Sheet1").Cells(1,8).EntireColumn.Select
    If posting code please use code tags.

  3. #3
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Phil,

    Like this:
    Code:
    Sub Test() 
        MsgBox ColLetter(Range("AA2").Column) 
    End Sub 
    
    Function ColLetter(iCol As Integer) As String 
    'get column letter from the column number 
    
        Select Case iCol 
            Case 1 To 256 'OK 
                ColLetter = Left$(Columns(iCol).Address _ 
                    (rowabsolute:=False, columnabsolute:=False), (iCol <= 26) + 2) 
                '(iCol <= 26) evaluates to either 0 (False) or -1 (True) 
            Case Else 'Wrong col ref 
                ColLetter = "Error : Invalid column number" 
        End Select 
    
    End Function
    HTH
    Richie

  4. #4
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,753
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default

    To get the column number from the column letter (you could use any valid row reference here, I'm using 1):

    Msgbox Range("H1").Column

    or with a variable:

    Sub test()
    Dim c As String
    c = "H"
    MsgBox Range(c & "1").Column
    End Sub
    Office 2010/365

  5. #5
    New Member
    Join Date
    Jul 2005
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    [deleted]

    Thanks so much. -Phil

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default

    Sorry you want the number from the letter. Didn't catch that, folk normally want it the other way round.



    If posting code please use code tags.

  7. #7
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Quote Originally Posted by Norie
    Sorry you want the number from the letter. Didn't catch that, folk normally want it the other way round.



    Ditto
    Richie

Some videos you may like

User Tag List

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
  •