Return column letter, rather than number?

[Ed]

Dim rng As Range
Dim cll As Range
Set rng = Selection

For Each cll In rng.Cells
MsgBox "Cell " & cll.Column & cll.Row & "has a value of " & cll.Value
Next cll

Is there a way to format the .Column property to show the column letter
designation instead of returning the column number?

Ed

 

[Guest]

No, however try this:
Msgbox cll.Address(False,False) & " Has a value of: " & cll

 

[Alan]

Good question to which I believe the answer is no.

I've only managed to return an alpha reference by
a) using the address property and then striping out the Alpha portion
whilst avoiding the $s (unless you use rowabsolute/column absolute) or
b) by converting the returned column ref to an ASCII character
reference - but if you are only using the first 26 columns or
c) by using the returned reference number to look up the alpha
reference in an predefined array - it's a pain setting up the array if
you expect to return columns with "high" ref numbers

I hope someone else knows better!

Alan

 

[Ed]

Thanks for the reply, Alan. Stripping out the Alpha from the Address seems
to be the answer.
Ed

 

[Ed]

Thanks, Charles. I appreciate the boost.
Ed

No, however try this:
Msgbox cll.Address(False,False) & " Has a value of: " & cll