Convert column number to letter

[frustrated123]

I can obtain the columns numbers but I cannot get the letters. Is there anyway to convert from a number to a letter?
eg. somefunction(1) gives me column(A) as an answer?

Thanks.

 

[Aladin Akyurek]

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

=SUBSTITUTE(ADDRESS(1,COLUMN(A2),4),"1","")

 

[SIXTH SENSE]

hi!
good only for column A to Z

=CHAR(64+COLUMN(A2))

 

[DonkeyOte]

Aladin's code works fine...

Address function works as Address(row,column) to return an absolute reference

eg

Address(1,1) = $A$1

All you need to do is ADD the number of columns you want to the column reference

ie

=SUBSTITUTE(ADDRESS(1,COLUMN()+64,4),"1","")

and it will return BM.

Nice Aladin as per usual

 

[frustrated123]

Thanks for the help guys. I initially wanted to do this so I could move along the column putting a header in each populated column as i thought Range("A1") was the only way. Since you were asking why I realised there must be another way and now I am using cells(row,col).

Thanks again.

 

[ken2step]

Are you using this formula then

=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(B1),"")

in say Cell B4 to get the result "B"

 

[frustrated123]

No I have just set up a for loop that goes through all my columns inserting a number until it gets to the last one. eg.

For x=1 TO NumOfCols
Cells(1,x).value = x


Something like that
I dont have the code with me.

 

[DougStroud]

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

Could I get someone to explain the following portion of the formula/function and how it is converting the arguments from Aladin's formula?
COLUMN(),4),"1","")

I see Address is using 1 as the row reference.
Column() w/out any argument and then 4 as an abs_num is the first part of the formula I do not understand.

Thanks,

ds

 

[PA HS Teacher]

Doug,
Address(rownumber,columnnumber) returns a string with the address of the cell equivalent of ActiveSheet.Cells(rownumber,ColumnNumber). The thir argument in the address function forces the Address function to return a string representing a relative reference.

For example,
=Address(1,2,4) would return "B1"

Because the rownumber fed to the address function in this case is always 1, the substitute function is used to replace 1 with "", leaving the column Letter(s).

[Edit]
=Column() returns the column number of the hose cell, so
=Address(1,2,4) would return a string containing the relative reference of the first cell in the column of the host cell.

 

[DougStroud]

Thanks PA,
That helped explain perfectly.

Cheers,

Doug