Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    VBA for column letter

    I found and adapted this code fragment to work out the column letter from the column number, (as long as the column letter is ZZ or less):

    Code:
    If columnnumber > 26 Then
        columnletter = Chr(Int((columnnumber - 1) / 26) + 64) & Chr(Int((columnnumber - 1) Mod 26) + 65)
      Else
        columnletter = Chr(columnnumber + 64)
    End If
    Is there a less clunky way of doing this ?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:
    mid(application.worksheetfunction.substitute(cells (1,columnnumber).address,"$1",""),2)

    Steve
    PS Though I am not sure why you would need the column letter, the column number is easy enough to use ...
    PPS in Excel you can use the formula, without resorting to VB:
    =SUBSTITUTE(ADDRESS(1,ColumnNumber,4),"1","")
    Last edited by sdckapr; 2013-05-03 at 14:08.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thank you Steve - less clunky than my version

    I'm extracting the column letter simply because I am not familiar with the syntax of using column numbers in VBA !

    To be exact, I am looking up the column in which a single cell range name is located, then in that column I am searching for a particular value between specific limits ie from a given starting and finishing row. It is all working fine, but I will now go and look up the syntax for using column numbers instead of letters - I agree that that might be tidier.
    Last edited by MartinM; 2013-05-03 at 17:38.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Martin

    Just to clarify:
    Steve's method will work for all versions of Excel.
    Your code snippet will not work for the three-letter columns available in Excel2007 onwards.

    zeddy

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Indeed Mr Z, hence my comment "as long as the column letter is ZZ or less"

    I'm getting on well with learning how to use numeric row and column references in VBA - its certainly makes for tidier, albeit slightly less easy to "read".

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Martin

    Fancy me missing the ZZs.
    My point was to congratulate Steve for his all-versions method.
    As far as using numeric refs in vba, I totally agree with you - it may be neater but I believe 'easier-to-follow' often trumps this.
    However, there are many occasions when it is just more efficient to use numeric refs, particularly if you have any loops which can't be avoided.

    zeddy

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Well thanks everyone.

    I've completely re-coded this module. The code is far shorter and neater, hence less error-prone, and I've got round the readability issue by using meaningful variable names for the rows and columns in the procedures.

    Nice.

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    A really simple way to retrieve a selected cell's column as an alpha string in vba is:
    Split(Selection.Address, "$")(1)
    Just as easily applied to range objects.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. The Following User Says Thank You to macropod For This Useful Post:

    sdckapr (2013-05-05)

  10. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks Paul - I do still sometimes have to do this, and yours is the simplest code . . . . so far.

    The SPLIT function is new to me - further research required on my part.

  11. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    The Split function basically creates an array from whatever you feed it, using the nominated character as a field delimiter. Since '.Address' returns absolute addresses (eg $A$1), using '$' as the delimiter splits it into a 3-element array consisting of nothing, A and 1. The number in parentheses after the Split function tells vba which element to retrieve from the Split's 0-based array.

    I doubt you will find anything simpler.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Split is much less clunky than using the substitute, mid combo I used. Thanks for the tip

    Steve

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Even easier is:
    MsgBox Selection.Column

    Maud

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Maudibe

    That would be great if you wanted the column number.
    But not so great if you want the column letter.

    zeddy

  15. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Selection.Column gives the column NUMBER not the column LETTER(s). The premise of the question was that we already had the columnnumber and with that how do we find the column letter(s).

    Steve

  16. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Yes, What was I thinking?

    Here is a function from Microsoft's website
    =ConvertToLetter(number)

    Code:
    Function ConvertToLetter(iCol As Integer) As String
       Dim iAlpha As Integer
       Dim iRemainder As Integer
       iAlpha = Int(iCol / 27)
       iRemainder = iCol - (iAlpha * 26)
       If iAlpha > 0 Then
          ConvertToLetter = Chr(iAlpha + 64)
       End If
       If iRemainder > 0 Then
          ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
       End If
    End Function
    Last edited by Maudibe; 2013-06-20 at 13:57. Reason: TESTED CODE AND IT IS NOT RELIABLE BEYOND COL AZ

Posting Permissions

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