| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extracting First Letter | New Users to Excel | |||
| How to replace column letter in refferences with a function using the old column letter? | Links and Linking in Excel | |||
| How to replace column letter in refferences with a function using the old column letter? | Excel Worksheet Functions | |||
| column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) | |||
| Extracting the column letter and row number | Excel Programming | |||
Linear Mode
