Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    A useful piece of paper to print off?

    I don't know about you lot, but I am forever trying to work out, from the Excel column headings, what the column number is (for example, when doing vlookups). So I wrote a quick program to print out all the column headers and their numbers up to IZ. I printed it off and I can now quickly glance at it to do the conversion. Just add nice lines around it all and it's there for ever.

    The code to do it is here for you to use:

    Code:
    Sub test2()
    
        Set w2data = Worksheets(1) ' the first sheet - change this to something else if you want
    
        For icol = 1 To 5
            For jrow = 1 To 26
                w2data.Cells(jrow, icol * 2 - 1) = jrow + ((icol - 1) * 26)
                w2data.Cells(jrow, icol * 2) = ColumnLetter(jrow + ((icol - 1) * 26))
            Next jrow
        Next icol
        For icol = 1 To 5
            For jrow = 1 To 26
                w2data.Cells(jrow + 27, icol * 2 - 1) = jrow + ((icol - 1) * 26) + 130
                w2data.Cells(jrow + 27, icol * 2) = ColumnLetter(jrow + ((icol - 1) * 26) + 130)
            Next jrow
        Next icol
    End Sub
    
    Function ColumnLetter(ColumnNumber As Integer) As String
        'Convert a column number to it's letter format
        If ColumnNumber > 26 Then
            ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & Chr(((ColumnNumber - 1) Mod 26) + 65)
        Else
            ColumnLetter = Chr(ColumnNumber + 64)
        End If
    End Function
    Have fun

    Alan

  2. The Following User Says Thank You to alan sh For This Useful Post:

    Maudibe (2014-02-26)

  3. #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
    Here is a function that will work with all columns from 1 to 16384, not just to 702:

    Code:
    Function ColLetter(iCol As Integer) As String
      ColLetter = Cells(1, iCol).Address(False, False)
      ColLetter = Left(ColLetter, Len(ColLetter) - 1)
    End Function
    Steve
    PS an Excel formula to do this could be something like
    =SUBSTITUTE(ADDRESS(1,A1,4),"1","")

    where A1 would have the Column Number you want to get the column letter for
    Last edited by sdckapr; 2014-02-26 at 13:24.

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Hey Y'all,

    Here's some code I keep in my Peronal.xls workbook it can be called either from code or a workbook.
    Code:
    '                         +-------------------------+             +----------+
    '-------------------------|     lColLtrToNum()      |-------------| 10/10/13 |
    '                         +-------------------------+             +----------+
    'Arguments: Column Reference as String
    '       Ex: "bba"
    
    Public Function lColLtrToNum(zCol As String) As Long
    
    'Function returns zero (0) if the column reference
    'given is out of range.
    
    'Note: If the cursor is in the cell you want the number
    '      for simply typing ?Activecell.column in the
    '      Immediate window will return the correct number.
    
       Dim iColLen  As Integer
       Dim iCnt     As Long
       Dim zCurChar As String
       
       zCol = UCase(zCol)
       iColLen = Len(zCol)
       
       If iColLen = 0 Or _
          iColLen > 3 Then Exit Function
       
       For iCnt = 1 To iColLen
          zCurChar = Mid(zCol, iCnt, 1)
          If zCurChar < Chr(65) Or _
             zCurChar > Chr(90) Then
            lColLtrToNum = 0
            Exit Function
          Else
            lColLtrToNum = lColLtrToNum + _
               (Asc(zCurChar) - 64) * (26 ^ (iColLen - iCnt))
          End If
       Next iCnt
       
    '*** Check to see if zCol value is greater than this
    '*** version of Excel supports
       If lColLtrToNum > Columns.Count Then lColLtrToNum = 0
       
    End Function   'lColLtrToNum
    ColLtrToNum.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #4
    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
    You can just use:
    =COLUMN(INDIRECT("AAA"&1))

    Without all the code and less to type...

    or if you want a UDF, a much shorter one is:

    Code:
    Function ColLtrToNum(sCol As String) As Integer
      ColLtrToNum = Range(sCol & "1").Column
    End Function
    Steve

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Steve,

    Very nice! It is short however:
    SteveError.JPG
    It chokes if given a value out of range (Excel 2003 in the example).
    This can be a problem when moving things between versions, sometimes longer is better. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    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
    What value do you want if it is out of range? That can be put into the function. If you are in XL2003 you probably wouldn't be needing columns > "IV"

    Steve

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Steve,

    I've set my function to check for the size of the workbook then compare against the generated column number if out range return 0.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    ..you said Steve's was short.

    This is shorter than your 36-foot RV:

    Code:
    Function ColLtrToNum(z As Range) As Integer
    ColLtrToNum = z.Column
    End Function
    ..and because you need to 'point' to a cell in the column you want the number for, it works in all Excel versions.

    ..but then again, why not just use
    =COLUMN(xxx) as Steve suggests, where xxx is a cell in the column you want the number for???

    zeddy
    Last edited by zeddy; 2014-02-27 at 05:49.

  10. #9
    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
    The shorter version works if the cell referenced is in the column you are interested in. The version I provided is more general, where the cell reference contains a text string of the Column Letter.

    Steve

  11. #10
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    The reason I created the original is so I could quickly work out, from a number, what column it was - or the other way around. Makes it easy when coding cell references. As I said, I printed the results out and it's pinned to the wall near my desk. I have not needed anything more than 260 columns.

    I just assumed others may want something similar. I've attached the output just in case you don't want to code it yourself.
    Attached Files Attached Files
    Last edited by alan sh; 2014-02-27 at 10:57.

Posting Permissions

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