In this article, we will create two custom functions, one function to convert column numbers to column references and other function to convert column references to column numbers.
Raw data for this example consists of two sample data, one sample data contains random column numbers and the second sample data contains random column references.
We have created two custom functions “ColNoToColRef” and “ColRefToColNo”. “ColNoToColRef” function is used to convert column numbers to column references. It takes integer values as input. Similarly, “ColRefToColNo” function is used to convert column references to column numbers.
Cells(1, ColNo).Address(True, False, xlA1)
Above code is used to get the address of the specified cell.
Left(ColNoToColRef, InStr(1, ColNoToColRef, "$") - 1)
Above code is used to extract the character to the left side of the symbol “$”.
Please follow below for the code
Option Explicit Function ColNoToColRef(ColNo As Long) As String If ColNo < 1 Or ColNo > Columns.Count Then ColNoToColRef = "Invalid input value" Else 'Finding the address of cell in the first row based on the specified column number ColNoToColRef = Cells(1, ColNo).Address(True, False, xlA1) 'Extracting the column name from the address ColNoToColRef = Left(ColNoToColRef, InStr(1, ColNoToColRef, "$") - 1) End If End Function Function ColRefToColNo(ColRef As String) Dim Rng As Range On Error GoTo LastPart 'Assigning cell in the first row of the specified column reference as range Set Rng = Range(ColRef & "1") 'Getting the column number of the specified range ColRefToColNo = Rng.Column Exit Function LastPart: ColRefToColNo = "Invalid input value" End Function
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.