VBA to Convert column number to letter

[Whisperer14]

What is the efficient way to convert a known column number to its corresponding letter - with acknowledgements to Aladin for the R1C1 formula...

Lastcol is the known column number, iStr a string variable

    Cells(1, LastCol).FormulaR1C1 = "=SUBSTITUTE(ADDRESS(1,COLUMN(),4),""1"","""")"
    iStr = Cells(1, LastCol).Value
    Cells(1, LastCol).ClearContents  'just to tidy up the sheet
    Columns("E" & iStr).Copy
    etc...

TIA GT

 

[RoryA]

Why do you need to as a matter of interest?

 

[Norie]

Why do you need the letter for the column?

Can't you work with the number?

 

[VoG]

Hi Whisperer.

As said above you should be able to use the number but 'just for fun':

Function ColLet(x As Integer) As String
With ActiveSheet.Columns(x)
    ColLet = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
End With
End Function

and to test it

Sub test()
MsgBox ColLet(35)
End Sub

 

[Whisperer14]

Hi All,
Thanks 4 the replies but when I went for the line:=

Columns(5,Lastcol).Copy - it threw a wobbly so I decided to go for the letters, so....

Reasking the question - What VBA should I have used to select the columns between 5 and lastcol?

 

[VoG]

Try

Range(Columns(5), Columns(lastcol)).Copy

 

[Whisperer14]

Re: VBA to Convert column number to letter CLOSED

As it says in my signature block - Never too old to learn - basics doooohhhh, where is that embarassed icon..... got it -

Thanks for the refresher

GT

 

[VoG]

No need to be embarrassed. In my opinion that syntax is far from intuitive considering that the following works with rows:

Rows("5:" & lastrow).Copy

 

[Whisperer14]

That does make me feel better...

 

[tomcatonnet99]

Hi Whisperer.

As said above you should be able to use the number but 'just for fun':

Function ColLet(x As Integer) As String
With ActiveSheet.Columns(x)
    ColLet = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
End With
End Function

and to test it

Sub test()
MsgBox ColLet(35)
End Sub

to make fun a bit more fun....



Private Sub CommandButton1_Click()
'assuming the row in question is row(1) and the column needs to be determined and the next free cell in that row needs to be used... ( ofcourse you'd remove the 'offset' statement if the last used cell needs to be referenced)

lstcol = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column
colrng = (Mid(Cells(1, lstcol).Address, 2, 1) & 1)
MsgBox (colrng)
Range(colrng) = "this is the next available cell in this row

End Sub