Convert column number to the column letter for message box

Dallie

New Member
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?

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

Thanks!

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

shg

MrExcel MVP
Code:
``````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.

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

Joe4

Here is one way:
Code:
``````Dim addr As String
Dim c As String

MsgBox "Check column " & c``````

jim may

Well-known Member
Here's a UDF - Paste into a Standard Module.

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

Code:
``````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``````

Thankyou all!

Replies
2
Views
100
Replies
8
Views
45
Replies
10
Views
101
Replies
4
Views
68
Replies
0
Views
35