Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column index returned - how to get the Letter
x = ActiveCell.Column
Returns 1 for column A How do I get x = A? Hmmm Arturo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column index returned - how to get the Letter
Arturo,
Function GetColumn(ByRef lngColumnNum As Long) As String GetColumn = Application.Substitute(Cells(1, _ lngColumnNum).Address(False, False), "1", vbNullString) End Function -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Arturo" wrote in message x = ActiveCell.Column Returns 1 for column A How do I get x = A? Hmmm Arturo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column index returned - how to get the Letter
One way:
Option Explicit Function ColLetter(rng As Range) As String Dim myStr As String With rng.Parent myStr = .Cells(1, rng.Column).Address(0, 0) myStr = Left(myStr, Len(myStr) - 1) End With ColLetter = myStr End Function And I could test it with: Sub testme() MsgBox ColLetter(ActiveCell) End Sub Arturo wrote: x = ActiveCell.Column Returns 1 for column A How do I get x = A? Hmmm Arturo -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column index returned - how to get the Letter
Thank you.
Dim arr As Variant arr = Split(ActiveCell.Address, "$") MsgBox arr(1) "Dave Peterson" wrote: One way: Option Explicit Function ColLetter(rng As Range) As String Dim myStr As String With rng.Parent myStr = .Cells(1, rng.Column).Address(0, 0) myStr = Left(myStr, Len(myStr) - 1) End With ColLetter = myStr End Function And I could test it with: Sub testme() MsgBox ColLetter(ActiveCell) End Sub Arturo wrote: x = ActiveCell.Column Returns 1 for column A How do I get x = A? Hmmm Arturo -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column index returned - how to get the Letter
thanks.
Dim arr As Variant arr = Split(ActiveCell.Address, "$") MsgBox arr(1) "Jim Cone" wrote: Arturo, Function GetColumn(ByRef lngColumnNum As Long) As String GetColumn = Application.Substitute(Cells(1, _ lngColumnNum).Address(False, False), "1", vbNullString) End Function -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Arturo" wrote in message x = ActiveCell.Column Returns 1 for column A How do I get x = A? Hmmm Arturo |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column index returned - how to get the Letter
That won't work in XL 97. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Arturo" wrote in message thanks. Dim arr As Variant arr = Split(ActiveCell.Address, "$") MsgBox arr(1) "Jim Cone" wrote: Arturo, Function GetColumn(ByRef lngColumnNum As Long) As String GetColumn = Application.Substitute(Cells(1, _ lngColumnNum).Address(False, False), "1", vbNullString) End Function -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Arturo" wrote in message x = ActiveCell.Column Returns 1 for column A How do I get x = A? Hmmm Arturo |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column index returned - how to get the Letter
Was this a test?
Arturo wrote: Thank you. Dim arr As Variant arr = Split(ActiveCell.Address, "$") MsgBox arr(1) "Dave Peterson" wrote: One way: Option Explicit Function ColLetter(rng As Range) As String Dim myStr As String With rng.Parent myStr = .Cells(1, rng.Column).Address(0, 0) myStr = Left(myStr, Len(myStr) - 1) End With ColLetter = myStr End Function And I could test it with: Sub testme() MsgBox ColLetter(ActiveCell) End Sub Arturo wrote: x = ActiveCell.Column Returns 1 for column A How do I get x = A? Hmmm Arturo -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column index returned - how to get the Letter
That would be the slowest way to do it. Enjoy.
-- Regards, Tom Ogilvy "Arturo" wrote in message ... Thank you. Dim arr As Variant arr = Split(ActiveCell.Address, "$") MsgBox arr(1) "Dave Peterson" wrote: One way: Option Explicit Function ColLetter(rng As Range) As String Dim myStr As String With rng.Parent myStr = .Cells(1, rng.Column).Address(0, 0) myStr = Left(myStr, Len(myStr) - 1) End With ColLetter = myStr End Function And I could test it with: Sub testme() MsgBox ColLetter(ActiveCell) End Sub Arturo wrote: x = ActiveCell.Column Returns 1 for column A How do I get x = A? Hmmm Arturo -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column letter to index | Excel Discussion (Misc queries) | |||
Translate Column Index Num to Letter | Excel Discussion (Misc queries) | |||
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) |