This is a discussion on Convert column number to letter within the Excel Questions forums, part of the Question Forums category; I can obtain the columns numbers but I cannot get the letters. Is there anyway to convert from a number ...
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?
good only for column A to Z
There is always a better way!!
Aladin's code works fine...
Address function works as Address(row,column) to return an absolute reference
Address(1,1) = $A$1
All you need to do is ADD the number of columns you want to the column reference
and it will return BM.
Nice Aladin as per usual
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).
Are you using this formula then
in say Cell B4 to get the result "B"
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.
Could I get someone to explain the following portion of the formula/function and how it is converting the arguments from Aladin's formula?
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.
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.
=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).
=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.
That helped explain perfectly.
my site: www.ecboardco.com
was built w/ a tremendous amount of help and guidance from mrexcel and a few dedicated board members: A Huge Thanks!