Excel

Column Numbers and Column Letters

Ease of Use

Easy

Version tested with

2000 

Submitted by:

mvidas

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:

instructions for use

			

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 'CODE: Left(Cells(1, v).Address(1, 0), InStr(1, Cells(1, v).Address(1, 0), "$") - 1) ' Cells(1, v).Address(1, 0) - Returns cell address in form AB$1 ' InStr(1, celladdress, "$") - Position of $ in cell address ' Left(celladdress, positionof$ -1) - Returns everything to left of $, which is col letter If ActiveWorkbook Is Nothing Then Application.ScreenUpdating = False Workbooks.Add 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 Workbooks.Add 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:

Column Letters.zip 10.55KB 

Approved by mdmackillop


This entry has been viewed 235 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express