Daily Dose of Excel

10 Comments

1. Andy Pope:

   How about,

   Function ColNum(ByVal ColumnLetter As String) As Long
   On Error Resume Next
   ColNum = Range(ColumnLetter & “1″).Column
   End Function

   7 June 2004, 9:09 am

2. CJM:

   Apologies for the formatting, the indents seem to fall out when it gets posted…

   =======================================

   Function ColNum(ByVal ColumnLetter As String) As Long
   ‘Returns the column reference if valid. If reference is
   ‘invalid, returns 0.
   ColNum = 0
   If Len(ColumnLetter) = 2 Then
   If ValidColumn(Left(ColumnLetter, 1), False) And _
   ValidColumn(Right(ColumnLetter, 1), True) Then
   ColNum = ThisWorkbook.Sheets(1).Range(ColumnLetter & “1″).Column
   Else
   ColNum = 0
   End If
   ElseIf Len(ColumnLetter) = 1 Then
   If ValidColumn(Left(ColumnLetter, 1), False) Then
   ColNum = ThisWorkbook.Sheets(1).Range(ColumnLetter & “1″).Column
   Else
   ColNum = 0
   End If
   End If
   End Function

   Function ValidColumn(TestChar As String, IVTest As Boolean) As Boolean
   ValidColumn = False
   If IVTest Then
   If Asc(UCase(TestChar)) >= 65 And Asc(UCase(TestChar)) < = 86 Then
   ValidColumn = True
   End If
   Else
   If Asc(UCase(TestChar)) >= 65 And Asc(UCase(TestChar)) <= 90 Then
   ValidColumn = True
   End If
   End If
   End Function

   7 June 2004, 9:25 am

3. David Wasserman:

   This function will work even if Microsoft expands the columns to allow three or more letters:

   Function ColNum(ByVal ColumnLetter As String) As Long
   Dim LenColLetter As Integer, i As Integer
   ColNum = 0: LenColLetter = Len(ColumnLetter)
   For i = 1 To LenColLetter
   ColNum = ColNum + (26 * (LenColLetter - i) * (Asc(UCase(Mid(ColumnLetter, i, 1))) - 64)) + 1
   Next i
   End Function

   7 June 2004, 9:52 am

4. David Wasserman:

   Oops, the 5th line should read:

   ColNum = ColNum + (26 ^(LenColLetter - i) * (Asc(UCase(Mid(ColumnLetter, i, 1))) - 64)) + 1

   The difference is power vs multiplication.

   7 June 2004, 10:03 am

5. Harald Staff:

   David

   THis is good. But I get “2″ as a result of “A”. After removing the +1 it said 1. I ran this, combining the two:

   Sub test()
   Dim L As Long, L2 As Long, S As String
   L = 0
   Do
   L = L + 1
   S = ColumnLetter(L)
   L2 = colNum(S)
   If L2 <> L Then
   MsgBox L & ” vs ” & L2, , S
   Exit Sub
   End If
   Loop Until L > 1000000
   MsgBox S, , L
   End Sub

   No error. So either both are great or both are equally wrong. Unfortunately I have only 256 columns to check against
   Best wishes Harald

   7 June 2004, 11:30 am

6. Dick:

   David: I think you need to get rid of the +1. ?ColNum(”a”) = 2.

   7 June 2004, 11:35 am

7. tjs:

   David,
   this eliminates the extra math. i.e. (26 * (LenColLetter - i)

   Function ColNum(ByVal ColumnLetter As String) As Long
   Dim LenColLetter As Integer, i As Integer
   ColNum = 0: LenColLetter = Len(ColumnLetter)
   For i = 1 To LenColLetter
   ColNum = ColNum * 26 + (Asc(UCase(Mid(ColumnLetter, i, 1))) - 64)
   Next i
   End Function

   7 June 2004, 11:51 am

8. Ron de Bruin:

   Try this

   Also from Chip I believe

   Function ColumnNumber(ColLetter) As Integer
   ColumnNumber = Cells(1, ColLetter).Column
   End Function

   Temp = ColumnNumber(”D”) ‘ returns 4

   7 June 2004, 1:32 pm

9. Gary Waters:

   Dick, here’s a function that includes error checking and covers all positive long integers:
   Function ColumnNumber(ByVal strCol As String) As Long
   Dim length As Long, i As Long
   strCol = UCase(strCol)
   length = Len(strCol)
   If length < 1 Or length > 7 Then
   ColumnNumber = -1
   Exit Function
   End If
   If Not strCol Like Replace(Space(length), ” “, “[A-Z]”) Then
   ColumnNumber = -1
   Exit Function
   End If
   If length = 7 Then
   If strCol > “FXSHRXW” Then
   ColumnNumber = -1
   Exit Function
   End If
   End If
   ColumnNumber = 0
   For i = 1 To length
   ColumnNumber = ColumnNumber + (Asc(Mid$(strCol, i, 1)) - 64) * 26 ^ (length - i)
   Next i
   End Function

   7 June 2004, 4:55 pm

10. Superplanilla:

    Otra variante:

    Function colnum(x)
    colnum = Range(x & “:” & x).Column
    End Function

    7 June 2004, 5:56 pm

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment