 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008Extracting 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 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 >Hello All, > [quoted text clipped - 11 lines] > >Rob You could do this... strLastColumn = Left(ActiveCell.Address(True, False), _ InStr(ActiveCell.Address(True, False), "$") - 1) Rick > Hello All, > [quoted text clipped - 13 lines] > > Rob This will do the same thing and is much more compact... strLastColumn = Split(ActiveCell.Address(True, False), "$")(0) Rick > You could do this... > [quoted text clipped - 20 lines] >> >> Rob On Wed, 14 May 2008 04:02:51 -0400, "Rick Rothstein \(MVP - VB\)" <rick.newsNO.SPAM@NO.SPAMverizon.net> 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 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 > On Wed, 14 May 2008 04:02:51 -0400, "Rick Rothstein \(MVP - VB\)" > <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote: [quoted text clipped - 10 lines] > > --ron On Wed, 14 May 2008 15:59:48 -0400, "Rick Rothstein \(MVP - VB\)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote: >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> Yup -- any non-zero converts to TRUE. --ron >>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> > > Yup -- any non-zero converts to TRUE. In past postings (over in the compiled VB newsgroups), I explained it this way... for evaluation of logical expressions, VB defines False to be zero and True to be Not False. Rick If you ever need to do this in the worksheet: =SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","")  SignatureGary''s Student - gsnu200786 > Hello All, > [quoted text clipped - 11 lines] > > Rob Provided he isn't using XL2007, of course.<g> Rick > If you ever need to do this in the worksheet: > [quoted text clipped - 17 lines] >> >> Rob Why not 2007?? SignatureGary''s Student - gsnu200786 > Provided he isn't using XL2007, of course.<g> > [quoted text clipped - 21 lines] > >> > >> Rob XL2007 has over 16000 columns available, so your code would only return the first two letters for Columns AAA through XFD. Rick > Why not 2007?? > [quoted text clipped - 25 lines] >> >> >> >> Rob But something like this should work: =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") > XL2007 has over 16000 columns available, so your code would only return the > first two letters for Columns AAA through XFD. [quoted text clipped - 35 lines] > >> >> > >> >> Rob SignatureDave Peterson Yep! That will work fine. Rick > But something like this should work: > [quoted text clipped - 42 lines] >> >> >> >> >> >> Rob
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.