Get column name from column number?

Question

I have several named columns on a sheet. I want to send the column number to a function that will return the column name.

For example, if column 1 is named "apple", I want to pass the column number 1 to a function that returns the column name "apple". My attempt:

Function getColName(colNumber As Integer) As String
'return column name when passed column number

    getColName = Cells(1, colNumber).Column.Name

End Function

How can I get this code to work?

Answer 1

Try this one :

set cell_address = Worksheet("Sheet1").Cells(1,1)
MsgBox cell_address.address(RowAbsolute:=False, ColumnAbsolute=False) 

'returns A1 You can use it in Range("A1") as well and get more functionality.

**Note**: RowAbsolute:=False, ColumnAbsolute=False are optional 

for more information:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-address-property-excel

for non-vba: https://exceljet.net/formula/convert-column-number-to-letter

Answer 2

ColumnNumber = i 'where i need to be a integer number > 0 and <= 16,384
ColumnName = cells(1,i).Address(False, False)

The parameters inside Adress() are related if the rows and columns references are going to be absolute or relatives. In this case above they'll be relatives.

And it's over. Simple like that.