Converting a number to a letter in Excel
 

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

Converting a number to a letter in Excel
 

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)


"Quinn Ramsey" wrote in message
...
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

Converting a number to a letter in Excel
 

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

On Wed, 16 Nov 2005 14:16:01 -0800, "Duke Carey"
wrote:

If the column number is in A1, you can use this formula (tested only a little
bit)

=IF(INT((A1)/26)0,CHAR(64+INT((A1)/26)),"")&CHAR(MOD(A1,26)+64)

"Quinn Ramsey" wrote:

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