Return column letter, rather than number?
 

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

Return column letter, rather than number?
 

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

"A good example is twice the value of good advice."


"Ed" wrote:

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

Return column letter, rather than number?
 

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 wrote:
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

Return column letter, rather than number?
 

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

"Alan" wrote in message
ps.com...
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 wrote:
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

Return column letter, rather than number?
 

Thanks, Charles. I appreciate the boost.
Ed

"Charles Chickering" wrote in
message ...
No, however try this:
Msgbox cll.Address(False,False) & " Has a value of: " & cll
--
Charles Chickering

"A good example is twice the value of good advice."


"Ed" wrote:

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