mapping columns

  • Thread starter Thread starter Farooq Sheri
  • Start date Start date
F

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
 
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
 
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)
 
Another way

Dim lngCol As Long

lngCol = 27
Msgbox Split(cells(1,lngCol).address,"$")(1)
 
Does this do what you want...

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