ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2024    Ole P. Erlandsen   All rights reserved
E-mail Contact Address