» Convert between column numbers and column references using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions The function below converts a number between 1 and 256 to a column reference between A and IV:

Function ColNo2ColRef(ColNo As Integer) As String
    If ColNo < 1 Or ColNo > 256 Then
        ColNo2ColRef = "#VALUE!"
        Exit Function
    End If
    ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)
    ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)
End Function
The function below converts a column reference (A - IV) to a column number between 1 and 256: 
Function ColRef2ColNo(ColRef As String) As Integer
    ColRef2ColNo = 0
    On Error Resume Next
    ColRef2ColNo = Range(ColRef & "1").Column
End Function