Return column name of a cell where the formula is written
To return a column name of a cell where the formula is written we need to apply a combination of Excel SUBSTITUTE, ADDRESS and COLUMN functions
Example: Return column name of a cell where the formula is written
|
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
|
The formula uses the Excel SUBSTITUTE, ADDRESS and COLUMN functions to return the column name of a cell where the formula has been written, which in this example is cell C4.
|
Sub Return_column_name_of_a_cell_where_the_formula_is_written()
'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 cell where the formula is written
ws.Range("C4") = Application.WorksheetFunction.Substitute(ws.Cells(1, ws.Range("C4").Column).Address(RowAbsolute:=False, ColumnAbsolute:=False), 1, "")
ws.Range("C4") = Application.WorksheetFunction.Substitute(ws.Cells(1, ws.Range("C4").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 ("C4") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Formula Cell: In this example the formula is written in cell ("C4") therefore the column function needs to refer to the same cell to return the column name of the cell that the formula is written in. The Output Range and the Formula Cell will always have to match to return the column name of the cell that the formula is written in.
Explanation about the formula used to return column name of a cell where the formula is written
EXPLANATION
EXPLANATION
To return a column name of a cell where the formula is written we need to apply a combination of Excel SUBSTITUTE, ADDRESS and COLUMN functions.
To return a column name of a cell where the formula is written we need to apply a combination of Excel SUBSTITUTE, ADDRESS and COLUMN functions.
FORMULAS
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
ARGUMENTS
(): For the formula to return a column name of a cell where the formula is written the COLUMN() needs to be left blank.