Return column name of a specific cell
To return a column name of a specific cell we need to apply a combination of Excel SUBSTITUTE, ADDRESS and COLUMN functions
Example: Return column name of a specific cell
|
=SUBSTITUTE(ADDRESS(1,COLUMN(E4),4),"1","")
|
The formula uses the SUBSTITUTE, ADDRESS and COLUMN functions to return the column name for a specific cell E4.
|
Sub Return_column_name_of_a_specific_cell()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to return the column name of a specific value held in row 4
ws.Range("C6") = Application.WorksheetFunction.Substitute(ws.Cells(1, ws.Range("E4").Column).Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, "")
ws.Range("C6") = Application.WorksheetFunction.Substitute(ws.Cells(1, ws.Range("E4").Column).Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, "")
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Worksheet Name: Have a worksheet named Analysis.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Specific Cell: Select the cell for which you want to return the column name by changing the cell reference ("E4") in the VBA code to any cell for which you want to return the column name in the Analysis worksheet, that doesn't conflict with the formula.
EXPLANATION
To return a column name of a specific cell we need to apply a combination of Excel SUBSTITUTE, ADDRESS and COLUMN functions.
To return a column name of a specific cell we need to apply a combination of Excel SUBSTITUTE, ADDRESS and COLUMN functions.
FORMULAS
=SUBSTITUTE(ADDRESS(1,COLUMN(cell),4),"1","")
=SUBSTITUTE(ADDRESS(1,COLUMN(cell),4),"1","")
ARGUMENTS
cell: The cell for which you want to return the column name.