Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21

Thread: Convert a column number to a column letter

  1. #1
    Join Date
    20th October 2005
    Location
    Ontario, CANADA
    Posts
    69

    Convert a column number to a column letter

    Hello everyone,

    I ned to convert a column letter to a column number for the following line of code in my VBA application:

    VB:
    strAverageBaseFormula = "=AVERAGE(D" & Trim(Str(lngFirstRowOfIncrement + 1)) & ":K" & Trim(Str(lngFirstRowOfIncrement + 1)) & ")" 
    
    
    I am inserting a formula into a cell using VBA, but the problem is that I don't know the letter value of column "K" (used in teh middle of the line); instead, I know it as column number 11. Is there a simple way to convert the column number 11 to the column letter "K"?

    Thanks!
    Bryan
    Last edited by bdonkersgoed; November 22nd, 2005 at 00:14.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    20th October 2005
    Location
    Ontario, CANADA
    Posts
    69

    Re: Convert a column number to a column letter

    Someone must know this?

    Basically, how do I convert "11" to "K"??

    Thanks,
    Bryan
    Last edited by bdonkersgoed; November 22nd, 2005 at 00:50.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    22nd November 2005
    Posts
    1

    Re: Convert a column number to a column letter

    VB:
    If MyColumn > 26 Then 
        MyColumnLetter = Chr(Int((MyColumn - 1) / 26) + 64) & Chr(((MyColumn - 1) Mod 26) + 65) 
    Else 
        MyColumnLetter = Chr(MyColumn + 64) 
    End If 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Convert a column number to a column letter

    Bryan

    Why not just use R1C1 notation for the formula?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,313

    Re: Convert a column number to a column letter

    One way,
    VB:
    =AVERAGE(D" & Trim(Str(lngFirstRowOfIncrement + 1)) & ":" & _ 
    Left(Cells(1, 11).Address(False, False), Len(Cells(1, 11).Address(False, False)) - 1) & Trim(Str(lngFirstRowOfIncrement + 1)) & ")" 
    
    

    Cheers
    Andy


  6. #6
    Join Date
    20th October 2005
    Location
    Ontario, CANADA
    Posts
    69

    Re: Convert a column number to a column letter

    Quote Originally Posted by tonyrosen
    VB:
    If MyColumn > 26 Then 
        MyColumnLetter = Chr(Int((MyColumn - 1) / 26) + 64) & Chr(((MyColumn - 1) Mod 26) + 65) 
    Else 
        MyColumnLetter = Chr(MyColumn + 64) 
    End If 
    
    
    Hehehe . . . thanks! I actually just wrote code almost identical to this, and was about to post it :P I thought there should be an easier way though.

    Thanks again!
    Bryan

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    20th October 2005
    Location
    Ontario, CANADA
    Posts
    69

    Re: Convert a column number to a column letter

    Quote Originally Posted by norie
    Bryan

    Why not just use R1C1 notation for the formula?
    Hmmmm . . . I guess that would have been the logical choice. I never use that notation though, and the spreadsheet that I am creating is for people who would not understand R1C1 at all.

    Thanks though!
    Bryan

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Convert a column number to a column letter

    Bryan

    The formula would appear in whatever notation the workbook was set to show.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    20th October 2005
    Location
    Ontario, CANADA
    Posts
    69

    Re: Convert a column number to a column letter

    Quote Originally Posted by norie
    Bryan

    The formula would appear in whatever notation the workbook was set to show.
    Oh, I didn't knwo that . . . thanks

    Too bad I just wrote a bunch of useless code then

    Regards,
    Bryan

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    23rd December 2011
    Posts
    1

    Re: Convert a column number to a column letter

    her is the easyest way, witch wil work with any number big

    VB:
    Public Function ColLett(Col As Integer) As String 
         
        If Col > 26 Then 
            ColLett = ColLett((Col - (Col Mod 26)) / 26) + Chr(Col Mod 26 + 64) 
        Else 
            ColLett = Chr(Col + 64) 
        End If 
         
    End Function 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 4 users browsing this thread. (0 members and 4 guests)

Possible Answers

  1. Change Column Letter Into A Column Number
    By Givemethemilk in forum EXCEL HELP
    Replies: 2
    Last Post: September 12th, 2007, 19:47
  2. Converting A Column Number Into A Column Letter
    By coolhandphil in forum EXCEL HELP
    Replies: 5
    Last Post: March 19th, 2007, 19:59
  3. Column Letter instead of number
    By brgipple in forum EXCEL HELP
    Replies: 5
    Last Post: August 18th, 2006, 11:04
  4. Replies: 5
    Last Post: January 31st, 2006, 23:34
  5. Convert Column number to Column Letter?
    By Localan in forum EXCEL HELP
    Replies: 2
    Last Post: December 14th, 2005, 13:31

Bookmarks

Posting Permissions

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