Converting a number to a letter in Excel

Q

Quinn Ramsey

I am working in excel and am placing a formula in diferent cells. I would
like to be able to convert a column number to the letter representation.
such as 1 would be A and 27 would be AA and so on. Here is the code I am
working with.

For col = 1 To 16 Step 1
If Cells(1, col) > Cells(1, 1) Then
Cells(4, col).Formula = "=LOOKUP(" & toLetter(col) & "4,2:2,3:3 )"
End If
Next col

It it the toLetter(col) that i need to work. I know that function doesnt
exist by that name. Any help on how to do this in excel would be greatly
appreciated. Thanks

-Quinn
 
B

Bob Phillips

You want VBA? Here is a function

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function




--

HTH

RP
(remove nothere from the email address if mailing direct)
 
G

Gord Dibben

Another method would be a User Defined Function

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

=GetColLet(27) returns AA

In your example..........

Dim col as Integer

Cells(4, col).Formula = "=LOOKUP(" & GetColLet(col) & "4,2:2,3:3 )"


Gord Dibben Excel MVP
 
Top