Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to extract just the Column Name of the active cell
Hello,
Does anyone know how to extract only the Column Name of an active cell in another cell. e.g. If active cell is D4 then I want the "D" to be extract and displayed in another cell say A1. Let me know. Regards, Fred |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to extract just the Column Name of the active cell
Debug.Print ColumnLetter(activecell.Column) '----------------------------------------------------------------- Function ColumnLetter(Col As Long) '----------------------------------------------------------------- Dim sColumn As String On Error Resume Next sColumn = Split(Columns(Col).Address(, False), ":")(1) On Error GoTo 0 ColumnLetter = sColumn End Function -- HTH RP (remove nothere from the email address if mailing direct) "Fred" wrote in message ... Hello, Does anyone know how to extract only the Column Name of an active cell in another cell. e.g. If active cell is D4 then I want the "D" to be extract and displayed in another cell say A1. Let me know. Regards, Fred |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to extract just the Column Name of the active cell
Not the best to look at, but it will work:
=LEFT(SUBSTITUTE(CELL("address"),"$",""),IF(ISNUMB ER(--(MID(SUBSTITUTE(CELL("address"),"$",""),2,1))),1,2 )) -- Regards, Dave "Fred" wrote: Hello, Does anyone know how to extract only the Column Name of an active cell in another cell. e.g. If active cell is D4 then I want the "D" to be extract and displayed in another cell say A1. Let me know. Regards, Fred |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to extract just the Column Name of the active cell
Hi Fred
You must use a event for this in the sheet module Try this Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column 26 Then Range("A1").Value = Left(Cells(Target.Row, Target.Column).Address(False, False), 2) Else Range("A1").Value = Left(Cells(Target.Row, Target.Column).Address(False, False), 1) End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Fred" wrote in message ... Hello, Does anyone know how to extract only the Column Name of an active cell in another cell. e.g. If active cell is D4 then I want the "D" to be extract and displayed in another cell say A1. Let me know. Regards, Fred |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to extract just the Column Name of the active cell
It doesn't update very well, but...
=CHAR(CELL("col")+64) "Fred" wrote: Hello, Does anyone know how to extract only the Column Name of an active cell in another cell. e.g. If active cell is D4 then I want the "D" to be extract and displayed in another cell say A1. Let me know. Regards, Fred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Find a empty cell in next column | Excel Discussion (Misc queries) | |||
Unable to set the active cell from VBA | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions |