» 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
CATEGORY - Cells, Ranges, Rows, and Columns in VBA
VERSION - All Microsoft Excel Versions
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
Book Store:
Recommended Books:
- Microsoft Access Version 2002 Step by Step
- Microsoft Outlook 2002 for Dummies
- Positioning: The Battle for Your Mind
- Special Edition Using Microsoft Access 2002
- 422 Tax Deductions for Businesses and Self-Employed Individuals : You Get a Raise Every Time You Find a Legitimate Tax Deduction
- Microsoft Windows XP Inside Out
No comments have been submitted.

