The only time I've wanted to use the letter was to inform the user of something.

"Please fix something in column 37"

is a problem for me and my A1 reference style setting/thinking.


Bob Phillips wrote:

Basically, if you want column letters you are stuck with using a function
such has been offered because it is built-on within VBA.

However, you aversion to column numbers is misplaced IMO. It is far easier
to work with numbers than letters when moving x columns on, looping through
a range of columns, etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Chrisso" wrote in message
ps.com...
Thanks to Richard & Bob for your replies.

Bob - thanks for your routine. I already have a sub that does this but
I dont like the fact that I have to use it all the time when the Range
object or VB should be able to do this for me. It complicates the code
and I have to cut and paste this sub into every workbook I work on.

Richard - thanks for your comments. The fact is I *want* to use column
letters! This, to me, is the most intuative approach as this is the way
you deal with cells and ranges in Excel proper. The fact that Range
objects only give column numbers is at odds with this approach. For
this same reason I think the Cells notation is equally clunky - even
more so as it expects rows first then column. Obviously a column number
is useful when you want to increment a column pointer but, in my view,
good for little else.

So - does this mean that there is no way around this then to keep using
a utitlity subroutine all the time? I am getting sick of having to cut
and paste it into every spreadsheet I work on - I cant keep in my
Personal book as other people have to have access to the subroutine as
well.

Chris


Bob Phillips wrote:
'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function


MsgBox ColumnLetter(27)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Chrisso" wrote in message
ups.com...
I am reasonably new to VB programming for Excel so I must be missing
something - hopefully you can help.

I use a lot of Range objects and I construct them with cell references
i.e. Range("A1"). I tend to create Range objects using the column from
another Range object

It seems I can only get a column *number* from Range objects
(myRange.Column) . This means I have to use a sub-routine I found on
this newsgroup to translate this number to a column *letter* which is
what I need to create another Range object.

I also need column letters for feedback to the user as they deal in
column letters and *not* numbers.

So everytime I work on a new project I have to carry this column
number
to column letter conversion subroutine with me - this does not seem
right! There must be some part of the language that will do this for
me
but I have not found it.

What am I missing? Am I using Range objects incorrectly? Is there a
way
to construct Range objects with column numbers?

Cheers for any ideas.

Chris



--

Dave Peterson