Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert column number to letter
Hi
You'll find all variations here, from short to "interesting": http://www.dicks-blog.com/archives/2...rs-to-letters/ HTH. Best wishes Harald "lvcha.gouqizi" skrev i melding oups.com... thanks. but how to implement the conversion in VBA code? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert column number to letter
Harald Staff wrote:
Hi You'll find all variations here, from short to "interesting": http://www.dicks-blog.com/archives/2...rs-to-letters/ Ooh, look how prescient I am. :) -- Dick Kusleika MVP - Excel Daily Dose of Excel http://www.dicks-blog.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert column number to letter
I cannot connect to this webpage. What's it for?
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert column number to letter
lvcha.gouqizi wrote:
I cannot connect to this webpage. What's it for? It's an excel blog. Try www.dailydoseofexcel.com -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert column number to letter
Here is a flexible VBA macro that can also be used on the Worksheet to convert a number to the correct column letters. Just load the code into a VBA module to use it. WORKSHEET FORMULA EXAMPLE: =ColumnLtr(38) The cell will contain "AL" This can also be used in VBA code VBA CODE USAGE: Col = ColumnLtr(256) Col will contain "IV" Code: -------------------- Public Function ColumnLtr(ByVal Column_Number As Long) As String 'Converts a number to a Column Letter Dim Ltr As String Dim N1 As Long Dim N2 As Long 'Maximum Column value is 256 If Column_Number 256 Then Column_Number = Colummn_Number Mod 256 'Convert to Column_Number Base 26 N1 = Column_Number \ 26 N2 = Column_Number Mod 26 'Convert number to Alpha characters If N1 < 0 Then Ltr = Chr$(64 + N1) End If If N2 = 0 Then Ltr = Ltr & "A" Else Ltr = Ltr & Chr$(64 + N2) End If ColumnLtr = Ltr End Function -------------------- -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=478915 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert column number to letter
Hello lvcha.gouqizi , Here is a flexible macro that can used as an Excel Worksheet Formula or in VBA code. It translates a number to its Excel column letter equivalent. It also checks the number. Numbers < 1 return an empty string, and numbers greater than 256 are divided by 256 and the remainder is used. WORKSHEET FORMULA: =ColumnLtr(40) The cell will contain "AN". VBA CODE: -<string- = ColumnLtr(<-long integer-) Code: -------------------- Public Function ColumnLtr(ByVal Column_Number As Long) As String 'Converts a number to a Column Letter Dim Ltr As String Dim N1 As Long Dim N2 As Long 'Column must greater than 0 Column_Number = Column_Number - 1 If Colimn_Number < 0 Then ColumnLtr = "" Exit Function End If 'Maximum Column value is 256 If Column_Number 256 Then Column_Number = Colummn_Number Mod 256 'Convert to Column_Number Base 26 N1 = Column_Number \ 26 N2 = Column_Number Mod 26 'Convert number to Alpha characters If N1 = 0 Then Ltr = "" Else Ltr = Chr$(64 + N1) End If Ltr = Ltr & Chr$(65 + N2) ColumnLtr = Ltr End Function -------------------- -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=478915 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert column number to letter
Awesome work!
Thank you very much, Leith. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert column number to letter
I think my suggestion is better, and it won't accept an invalid column, such
as 257. -- HTH RP (remove nothere from the email address if mailing direct) "lvcha.gouqizi" wrote in message ups.com... Awesome work! Thank you very much, Leith. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column Header - Convert a Number to a letter | Excel Discussion (Misc queries) | |||
convert column number to letter | Excel Programming | |||
Algorithm to convert Column Letter to Number | Excel Programming |