Excel

Column Numbers and Column Letters

Ease of Use

Easy

Version tested with

2000

Submitted by:

Description:

Find a column number by its letter, or find a column letter by its number

Discussion:

The function CNumber returns the column number of the letter given to it, and the function CLetter returns the column letter of the number given to it. For example, you could use this with the Cells method like Cells(1,CNumber("A")) will be the same as Cells(1,1) or Range("A1"). More for information than use, it can still come in handy if you want to create a table of letter/number combinations

Code:

```			Public Function CLetter(v As Long) As String
'The If statement is just checking to see if a workbook is active. This is because this
' function uses the .Address method, which returns the address of a given cell.  If no
' workbook is active, then it creates one, gets the value, and closes it
'Here is a breakdown of the different parts of the code
' Left(celladdress, positionof\$ -1) - Returns everything to left of \$, which is col letter
If ActiveWorkbook Is Nothing Then
Application.ScreenUpdating = False
CLetter = Left(Cells(1, v).Address(1, 0), InStr(1, Cells(1, v).Address(1, 0), "\$") - 1)
ActiveWorkbook.Close
Application.ScreenUpdating = True
Else
CLetter = Left(Cells(1, v).Address(1, 0), InStr(1, Cells(1, v).Address(1, 0), "\$") - 1)
End If
End Function
Public Function CNumber(v As String) As Integer
If ActiveWorkbook Is Nothing Then
Application.ScreenUpdating = False
CNumber = Range(v & "1").Column
ActiveWorkbook.Close
Application.ScreenUpdating = True
Else
CNumber = Range(v & "1").Column
End If
End Function
Sub EnterColumnLetters()
'Enters the column letter into row 1 on activesheet,
' and the corresponding number into row 2
Dim CLL As Range
Range("1:2").Insert
Range("1:2").NumberFormat = "@"
For Each CLL In Rows(1).Cells
CLL = CLetter(CLL.Column)
CLL.Offset(1, 0) = CNumber(CLL.Text)
Next CLL
'Columns.AutoFit
End Sub

```

How to use:

1. Copy above code.
2. In Excel press Alt + F11 to enter the VBE.
3. Press Ctrl + R to show the Project Explorer.
4. Right-click desired file on left (in bold).
5. Choose Insert -> Module.
6. Paste code into the right pane.
7. Press Alt + Q to close the VBE.
8. Save workbook before any other changes.

Test the code:

1. From Excel, go to Tools -> Macros -> Macros...
2. Select EnterColumnLetters to create a table of letters/numbers

Sample File:

Approved by mdmackillop

This entry has been viewed 244 times.

Copyright @2004 - 2014 VBA Express