# Thread: A useful piece of paper to print off?

1. ## A useful piece of paper to print off?

I don't know about you lot, but I am forever trying to work out, from the Excel column headings, what the column number is (for example, when doing vlookups). So I wrote a quick program to print out all the column headers and their numbers up to IZ. I printed it off and I can now quickly glance at it to do the conversion. Just add nice lines around it all and it's there for ever.

The code to do it is here for you to use:

Code:
```Sub test2()

Set w2data = Worksheets(1) ' the first sheet - change this to something else if you want

For icol = 1 To 5
For jrow = 1 To 26
w2data.Cells(jrow, icol * 2 - 1) = jrow + ((icol - 1) * 26)
w2data.Cells(jrow, icol * 2) = ColumnLetter(jrow + ((icol - 1) * 26))
Next jrow
Next icol
For icol = 1 To 5
For jrow = 1 To 26
w2data.Cells(jrow + 27, icol * 2 - 1) = jrow + ((icol - 1) * 26) + 130
w2data.Cells(jrow + 27, icol * 2) = ColumnLetter(jrow + ((icol - 1) * 26) + 130)
Next jrow
Next icol
End Sub

Function ColumnLetter(ColumnNumber As Integer) As String
'Convert a column number to it's letter format
If ColumnNumber > 26 Then
ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function```
Have fun

Alan

2. ## The Following User Says Thank You to alan sh For This Useful Post:

Maudibe (2014-02-26)

3. Here is a function that will work with all columns from 1 to 16384, not just to 702:

Code:
```Function ColLetter(iCol As Integer) As String
ColLetter = Left(ColLetter, Len(ColLetter) - 1)
End Function```
Steve
PS an Excel formula to do this could be something like

where A1 would have the Column Number you want to get the column letter for

4. Hey Y'all,

Here's some code I keep in my Peronal.xls workbook it can be called either from code or a workbook.
Code:
```'                         +-------------------------+             +----------+
'-------------------------|     lColLtrToNum()      |-------------| 10/10/13 |
'                         +-------------------------+             +----------+
'Arguments: Column Reference as String
'       Ex: "bba"

Public Function lColLtrToNum(zCol As String) As Long

'Function returns zero (0) if the column reference
'given is out of range.

'Note: If the cursor is in the cell you want the number
'      for simply typing ?Activecell.column in the
'      Immediate window will return the correct number.

Dim iColLen  As Integer
Dim iCnt     As Long
Dim zCurChar As String

zCol = UCase(zCol)
iColLen = Len(zCol)

If iColLen = 0 Or _
iColLen > 3 Then Exit Function

For iCnt = 1 To iColLen
zCurChar = Mid(zCol, iCnt, 1)
If zCurChar < Chr(65) Or _
zCurChar > Chr(90) Then
lColLtrToNum = 0
Exit Function
Else
lColLtrToNum = lColLtrToNum + _
(Asc(zCurChar) - 64) * (26 ^ (iColLen - iCnt))
End If
Next iCnt

'*** Check to see if zCol value is greater than this
'*** version of Excel supports
If lColLtrToNum > Columns.Count Then lColLtrToNum = 0

End Function   'lColLtrToNum```
ColLtrToNum.JPG
HTH

5. You can just use:
=COLUMN(INDIRECT("AAA"&1))

Without all the code and less to type...

or if you want a UDF, a much shorter one is:

Code:
```Function ColLtrToNum(sCol As String) As Integer
ColLtrToNum = Range(sCol & "1").Column
End Function```
Steve

6. Steve,

Very nice! It is short however:
SteveError.JPG
It chokes if given a value out of range (Excel 2003 in the example).
This can be a problem when moving things between versions, sometimes longer is better. HTH

7. What value do you want if it is out of range? That can be put into the function. If you are in XL2003 you probably wouldn't be needing columns > "IV"

Steve

8. Steve,

I've set my function to check for the size of the workbook then compare against the generated column number if out range return 0.

9. Hi RG

..you said Steve's was short.

This is shorter than your 36-foot RV:

Code:
```Function ColLtrToNum(z As Range) As Integer
ColLtrToNum = z.Column
End Function```
..and because you need to 'point' to a cell in the column you want the number for, it works in all Excel versions.

..but then again, why not just use
=COLUMN(xxx) as Steve suggests, where xxx is a cell in the column you want the number for???

zeddy

10. The shorter version works if the cell referenced is in the column you are interested in. The version I provided is more general, where the cell reference contains a text string of the Column Letter.

Steve

11. The reason I created the original is so I could quickly work out, from a number, what column it was - or the other way around. Makes it easy when coding cell references. As I said, I printed the results out and it's pinned to the wall near my desk. I have not needed anything more than 260 columns.

I just assumed others may want something similar. I've attached the output just in case you don't want to code it yourself.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•