Extracting Column Letter
 

Hello All,

I am trying to extract and use the column letter instead of the number. My
code looks something like this:

Dim StrLastColumn as String

strLastColumn = ActiveCell.Column

The problem with this is that the .Column property returns the number not
the letter. So, if I am in column Z I get 26 instead of Z. I really need Z.

Any thoughts?

Rob

Extracting Column Letter
 

Function GetCoLLet(ColNumber As Integer) As String
GetCoLLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber 26))
End Function

=getcollet(256) returns IV


Gord Dibben MS Excel MVP

On Tue, 13 May 2008 19:54:00 -0700, Rob wrote:

Hello All,

I am trying to extract and use the column letter instead of the number. My
code looks something like this:

Dim StrLastColumn as String

strLastColumn = ActiveCell.Column

The problem with this is that the .Column property returns the number not
the letter. So, if I am in column Z I get 26 instead of Z. I really need Z.

Any thoughts?

Rob

Extracting Column Letter
 

You could do this...

strLastColumn = Left(ActiveCell.Address(True, False), _
InStr(ActiveCell.Address(True, False), "$") - 1)


Rick


"Rob" wrote in message
...
Hello All,

I am trying to extract and use the column letter instead of the number.
My
code looks something like this:

Dim StrLastColumn as String

strLastColumn = ActiveCell.Column

The problem with this is that the .Column property returns the number not
the letter. So, if I am in column Z I get 26 instead of Z. I really need
Z.

Any thoughts?

Rob

Extracting Column Letter
 

This will do the same thing and is much more compact...

strLastColumn = Split(ActiveCell.Address(True, False), "$")(0)

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
You could do this...

strLastColumn = Left(ActiveCell.Address(True, False), _
InStr(ActiveCell.Address(True, False), "$") - 1)


Rick


"Rob" wrote in message
...
Hello All,

I am trying to extract and use the column letter instead of the number.
My
code looks something like this:

Dim StrLastColumn as String

strLastColumn = ActiveCell.Column

The problem with this is that the .Column property returns the number not
the letter. So, if I am in column Z I get 26 instead of Z. I really
need Z.

Any thoughts?

Rob

Extracting Column Letter
 

If you ever need to do this in the worksheet:

=SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","")

--
Gary''s Student - gsnu200786


"Rob" wrote:

Hello All,

I am trying to extract and use the column letter instead of the number. My
code looks something like this:

Dim StrLastColumn as String

strLastColumn = ActiveCell.Column

The problem with this is that the .Column property returns the number not
the letter. So, if I am in column Z I get 26 instead of Z. I really need Z.

Any thoughts?

Rob

Extracting Column Letter
 

Provided he isn't using XL2007, of course.<g

Rick


"Gary''s Student" wrote in message
...
If you ever need to do this in the worksheet:

=SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","")

--
Gary''s Student - gsnu200786


"Rob" wrote:

Hello All,

I am trying to extract and use the column letter instead of the number.
My
code looks something like this:

Dim StrLastColumn as String

strLastColumn = ActiveCell.Column

The problem with this is that the .Column property returns the number not
the letter. So, if I am in column Z I get 26 instead of Z. I really
need Z.

Any thoughts?

Rob

Extracting Column Letter
 

Why not 2007??
--
Gary''s Student - gsnu200786


"Rick Rothstein (MVP - VB)" wrote:

Provided he isn't using XL2007, of course.<g

Rick


"Gary''s Student" wrote in message
...
If you ever need to do this in the worksheet:

=SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","")

--
Gary''s Student - gsnu200786


"Rob" wrote:

Hello All,

I am trying to extract and use the column letter instead of the number.
My
code looks something like this:

Dim StrLastColumn as String

strLastColumn = ActiveCell.Column

The problem with this is that the .Column property returns the number not
the letter. So, if I am in column Z I get 26 instead of Z. I really
need Z.

Any thoughts?

Rob

Extracting Column Letter
 

XL2007 has over 16000 columns available, so your code would only return the
first two letters for Columns AAA through XFD.

Rick


"Gary''s Student" wrote in message
...
Why not 2007??
--
Gary''s Student - gsnu200786


"Rick Rothstein (MVP - VB)" wrote:

Provided he isn't using XL2007, of course.<g

Rick


"Gary''s Student" wrote in
message
...
If you ever need to do this in the worksheet:

=SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","")

--
Gary''s Student - gsnu200786


"Rob" wrote:

Hello All,

I am trying to extract and use the column letter instead of the
number.
My
code looks something like this:

Dim StrLastColumn as String

strLastColumn = ActiveCell.Column

The problem with this is that the .Column property returns the number
not
the letter. So, if I am in column Z I get 26 instead of Z. I really
need Z.

Any thoughts?

Rob

Extracting Column Letter
 

But something like this should work:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

"Rick Rothstein (MVP - VB)" wrote:

XL2007 has over 16000 columns available, so your code would only return the
first two letters for Columns AAA through XFD.

Rick

"Gary''s Student" wrote in message
...
Why not 2007??
--
Gary''s Student - gsnu200786


"Rick Rothstein (MVP - VB)" wrote:

Provided he isn't using XL2007, of course.<g

Rick


"Gary''s Student" wrote in
message
...
If you ever need to do this in the worksheet:

=SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","")

--
Gary''s Student - gsnu200786


"Rob" wrote:

Hello All,

I am trying to extract and use the column letter instead of the
number.
My
code looks something like this:

Dim StrLastColumn as String

strLastColumn = ActiveCell.Column

The problem with this is that the .Column property returns the number
not
the letter. So, if I am in column Z I get 26 instead of Z. I really
need Z.

Any thoughts?

Rob



--

Dave Peterson

Extracting Column Letter
 

Yep! That will work fine.

Rick


"Dave Peterson" wrote in message
...
But something like this should work:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

"Rick Rothstein (MVP - VB)" wrote:

XL2007 has over 16000 columns available, so your code would only return
the
first two letters for Columns AAA through XFD.

Rick

"Gary''s Student" wrote in
message
...
Why not 2007??
--
Gary''s Student - gsnu200786


"Rick Rothstein (MVP - VB)" wrote:

Provided he isn't using XL2007, of course.<g

Rick


"Gary''s Student" wrote in
message
...
If you ever need to do this in the worksheet:

=SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","")

--
Gary''s Student - gsnu200786


"Rob" wrote:

Hello All,

I am trying to extract and use the column letter instead of the
number.
My
code looks something like this:

Dim StrLastColumn as String

strLastColumn = ActiveCell.Column

The problem with this is that the .Column property returns the
number
not
the letter. So, if I am in column Z I get 26 instead of Z. I
really
need Z.

Any thoughts?

Rob



--

Dave Peterson

Extracting Column Letter
 

On Wed, 14 May 2008 04:02:51 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

strLastColumn = Split(ActiveCell.Address(True, False), "$")(0)

You can omit the True and make it even more "compact" :-))

Split(ActiveCell.Address(, False), "$")(0)

or even:

Split(ActiveCell.Address(, 0), "$")(0)

--ron

Extracting Column Letter
 

Yeah, I know... but there are certain situations where I shy away from using
default values when posting code to newsgroups where readers may not be
familiar with some of the one-liner constructions I come up with... commas
next to opening parentheses is one of them.... too many people tend to type
code from postings rather than copy/paste it (which I have **never** been
able to understand) and the isolated comma next to an opening parentheses
(being an unusual character combination) seems too easy to over look to me.
Maybe I am being too overprotecting? And, of course, I could have compacted
my own response like so...

Split(ActiveCell.Address(1, 0), "$")(0)

if I had really thought about it (yes, plus 1 will work in place of True
even though the value of True is actually -1).<g

Rick


"Ron Rosenfeld" wrote in message
...
On Wed, 14 May 2008 04:02:51 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

strLastColumn = Split(ActiveCell.Address(True, False), "$")(0)

You can omit the True and make it even more "compact" :-))

Split(ActiveCell.Address(, False), "$")(0)

or even:

Split(ActiveCell.Address(, 0), "$")(0)

--ron