Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 versus A1 in VB code
Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Column produces a
column NUMBER. How do I get back instead, or convert that to, the letter portion of the column label in A1 format? e.g. the last active cell is "Z42" The above code returns 26. I need it to return "Z". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 versus A1 in VB code
I do the following:
dim iColumn as integer, sColumn as String iColumn=26 sColumn = Chr(iColumn + 64) "Bill Sturdevant" wrote: Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Column produces a column NUMBER. How do I get back instead, or convert that to, the letter portion of the column label in A1 format? e.g. the last active cell is "Z42" The above code returns 26. I need it to return "Z". |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 versus A1 in VB code
But, if the last column is "DJ", my code returns 114. The proposed solution
below does not handle that. "Tony_VBACoder" wrote: I do the following: dim iColumn as integer, sColumn as String iColumn=26 sColumn = Chr(iColumn + 64) "Bill Sturdevant" wrote: Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Column produces a column NUMBER. How do I get back instead, or convert that to, the letter portion of the column label in A1 format? e.g. the last active cell is "Z42" The above code returns 26. I need it to return "Z". |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 versus A1 in VB code
try temp = ActiveCell.Address to get you started
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 versus A1 in VB code
Hi Bill,
If you use: Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Address (False, False) It will return: A1 If you use: Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Address It will return: $A$1 HTH--Lonnie M. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 versus A1 in VB code
Thank you! We are almost there! What I am really trying to get is the "A"
in the "A1"! "Lonnie M." wrote: Hi Bill, If you use: Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Address (False, False) It will return: A1 If you use: Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Address It will return: $A$1 HTH--Lonnie M. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 versus A1 in VB code
Most suggestions in this thread wont work for columns beyond 26, and you should make it a habit to try to make your code as universal and bullitproof as possible. As you'll probably be using it more often, wrap it in a function. usage like ?debug.print columnletter(activesheet.specialcells(xlCellTypeLa stcell).column) 'This one is "future proof", '(if MS ever decides to allow more than 256 columns) Function ColumnLetter(ByVal colNum As Long) As String Do ColumnLetter = Chr$(65 + (colNum - 1) Mod 26) & ColumnLetter colNum = (colNum - 1) \ 26 Loop While colNum 0 End Function 'This one is marginally faster but limited to two letter combinations. '(which works for all current versions). Function Column_Letter(ByVal colNum As Long) As String colNum = (colNum - 1) Mod 256 If colNum 25 Then Column_Letter = Chr$(64 + colNum \ 26) Column_Letter = Column_Letter & Chr$(65 + colNum Mod 26) End Function hth -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Bill Sturdevant wrote : Thank you! We are almost there! What I am really trying to get is the "A" in the "A1"! "Lonnie M." wrote: Hi Bill, If you use: Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Address (False, False) It will return: A1 If you use: Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Address It will return: $A$1 HTH--Lonnie M. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 versus A1 in VB code
Hi all, I found this post by Dana DeLouis--short, simple, and
brilliant--had to share it. Dana DeLouis Aug 28 2001, 7:55 pm Newsgroups: microsoft.public.excel.programming Letter = Split(ActiveCell.Address, "$")(1) Adapted to OP's example: Debug.Print Split(Selection.SpecialCells(xlCellTypeLastCell).A ddress, "$")(1) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
R1C1 versus A1 in VB code
Lonnie, I know that elegant solution. it may be brilliant, but my function column_letter is 10 times faster. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Lonnie M. wrote : Hi all, I found this post by Dana DeLouis--short, simple, and brilliant--had to share it. Dana DeLouis Aug 28 2001, 7:55 pm Newsgroups: microsoft.public.excel.programming Letter = Split(ActiveCell.Address, "$")(1) Adapted to OP's example: Debug.Print Split(Selection.SpecialCells(xlCellTypeLastCell).A ddress, "$")(1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question on this conversion code switching between r1c1 to A1 format tia sal2 | Excel Worksheet Functions | |||
Question on this conversion code switching between r1c1 to A1 format tia sal2 | Excel Discussion (Misc queries) | |||
R1C1 versus A1 | New Users to Excel | |||
What's wrong with this code (r1c1 paste) | Excel Programming | |||
Converting code to R1C1 format | Excel Programming |