Getting Column *Letters* From Range Objects

[Chrisso]

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

 

[RichardSchollar]

Hi Chris

You can certainly create range objects without column letters such as:

Cells(1,1).Value = 500

would assign the value 500 to A1 on the activesheet for example. Here,
the (1,1) refers to (Row Reference, Column Reference), so Cells(10,5)
refers to E10 for example.

If you want to create ranges over more than one cell, then you can use:

Range(Cells(1,1),Cells(10,5))

which refers to A1:E10. Using this syntax, you never have to worry
about what the column letter is.

You can use this kind of reference to return the column letter:

sColumnLetter =
left(cells(10,5).address(true,false),instr(1,cells(10,5).address(true,false),"$")-1)

Hope this helps!

Richard

 

[Bob Phillips]

'-----------------------------------------------------------------
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]

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

 

[Roger Govier]

Hi Chrisso

Try

Dim c As String
c = WorksheetFunction.Substitute(Cells(1, 30).Address(, False, 1), "$1",
"")

Which will return AD

 

[Chip Pearson]

There are lots of things that would be nice if VBA did them for you. I have
a long list. But given that you have work with what you have available, you
might try a function like

Function ColumnLetter(R As Range) As String
ColumnLetter = Left(R.Address(False, False), 1 - (R.Column > 26))
End Function

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Chip Pearson]

I should have added the code in my previous reply won't work in Excel 2007,
since column letters can be three characters. Instead, try the following. It
will work in any version of Excel.

Function ColumnLetter(R As Range) As String
ColumnLetter = Left(R.Address(False, False), _
1 - (R.Column > 26) - (R.Column > 702))
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

I have to agree with Bob. In addition, using strings is much slower than
working with numbers. I can't think of a single instance when I wanted to
use a column letter. You can work whatever way you want, but all the
problems you list go away when you use numbers.

 

[Dave Peterson]

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.