Return column letter, rather than number?

E

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
 
C

Charles Chickering

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

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
 
E

Ed

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top