mapping columns

[Farooq Sheri]

Greetings

Is there a way to map column number to alphabet such column 6 equates to "F"
and column equates to AA.

Thanks

 

[OssieMac]

Not really sure what you want here but perhaps the following will help. The
first 2 return the column number from the alpha id and the second 2 return
the column alpha id from the column number. Returning the Address (alpha id)
the parameters indicate whether to return absolute or relative. ,0 is
relative and 1 (or no parameter) returns absolute.

Sub test()

MsgBox Columns("AA").Column
MsgBox Columns("F").Column

MsgBox Columns(27).Address(, 0) 'Relative
MsgBox Columns(6).Address(, 1) 'Absolute
MsgBox Columns(6).Address() 'Absolute

End Sub

 

[OssieMac]

I should perhaps have expanded my answer to show you how to extract just the
alpha column id. I have used a variable for the position of the colon to make
it self explanatory but you could nest the entire forumla in one line of code.

Dim ColonPos As Integer
ColonPos = InStr(1, Columns(27).Address(, 0), ":")

MsgBox Left(Columns(27).Address(, 0), ColonPos - 1)

 

[Jacob Skaria]

Another way

Dim lngCol As Long

lngCol = 27
Msgbox Split(cells(1,lngCol).address,"$")(1)

 

[Rick Rothstein]

Does this do what you want...

ColumnNumber = 5
ColumnLetter = Split(Cells(1, ColumnNumber).Address(1, 0), "$")(0)