Convert column number to the column letter for message box

[Dallie]

Hi,

I have message box that I am trying to convert from a column number to column letter to make the code more user friendly as I was performing this on Column AC which is column 29 (much more confusing)

How would I go about converting the column number to column letter for this code?

c = Selection.column
If MsgBox("Check column " & c & "?", vbOKCancel) = vbOK Then

Thanks!

 

[shg]

Function ColLtr(ByVal iCol As Long) As String
  ' shg 2012
  ' Good for any positive Long
  If iCol > 0 Then ColLtr = ColLtr((iCol - 1) \ 26) & Chr(65 + (iCol - 1) Mod 26)
End Function

E.g.

If MsgBox("Check column " & ColLtr(c) & "?", vbOKCancel) = vbOK Then

 

[Joe4]

Here is one way:

Dim addr As String
Dim c As String

addr = Selection.Address(0, 0)
c = Left(addr, Len(addr) - Len(Selection.Row))
MsgBox "Check column " & c

 

[jim may]

Here's a UDF - Paste into a Standard Module.

Select a Cell in your worksheet, say M1; then run the macro Foo...

Sub Foo()
c = ColLetter(Selection.Column)
MsgBox "Check column " & c & "?"
End Sub


Function ColLetter(ColNumber As Integer) As String
    ColLetter = Left(Cells(1, ColNumber).Address(False, False), _
        1 - (ColNumber > 26))
End Function

 

[Dallie]

Thankyou all!