Convert between column numbers and column references

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
    On Error Goto 0
End Function

Document last updated 1999-12-20 12:51:27 Printerfriendly version

User comments:

Andrew H from Verden, Germany wrote (2006-07-24 09:44:55 CET):

Thankyou
Thankyou, thankyou, thankyou