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?

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

convert column number to letter
 

I cannot connect to this webpage. What's it for?

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

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

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

convert column number to letter
 

Awesome work!
Thank you very much, Leith.

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.