Column Numbers to Letters

This is extremely useful if you need to switch between the Range object and the Cells object.
Remember that using column letters in your code makes it significant easier to understand and quicker to debug.

Public Function Col_Letter(ByVal iColNo As Integer) As String 
Dim sstartletter As String
   On Error GoTo AnError
   Col_Letter = Left(Cells(1, iColNo).Address(False, False), _
                  Len(Cells(1, iColNo).Address(False, False)) - 1)
   Exit Function
AnError:
   Call MsgBox(Err.Number & " - " & Err.Description)
End Function

Public Function Col_Letter2(ByVal iColNo As Integer) As String 
Dim sstartletter As String
   On Error GoTo AnError
   Select Case iColNo
      Case 0: Col_Letter2 = Chr(90)
      Case Is <= 26: Col_Letter2 = Chr(iColNo + 64)
      Case Else
         If iColNo Mod 26 = 0 Then
            Col_Letter2 = Chr(64 + (iColNo / 26) - 1) & Col_Letter(iColNo Mod 26)
            Exit Function
         End If
         sstartletter = Chr(Int(iColNo / 26) + 64)
         Col_Letter2 = sstartletter & Col_Letter2(iColNo Mod 26)
   End Select
   Exit Function
AnError:
   Call MsgBox(Err.Number & " - " & Err.Description)
End Function

Converting a Column Letter to its equivalent Column Number

Public Function Col_Number(ByVal sColChar As String, _ 
                  Optional ByVal sWshName As String = "") _
                           As Integer
Dim istartnumber As Integer
   On Error GoTo AnError
   If Len(sColChar) = 1 Then
      If sWshName <> "" Then _
         Col_Number = Worksheets(sWshName).Range(sColChar & "1").Column
      If sWshName = "" Then Col_Number = Range(sColChar & "1").Column
   Else
      istartnumber = Range((Left(sColChar, 1)) & "1").Column
      Col_Number = ((istartnumber)) * 26 * (Len(sColChar) - 1) + _
                                      Col_Number(Right(sColChar, Len(sColChar) - 1))
   End If
   Exit Function
AnError:
   Call MsgBox(Err.Number & " - " & Err.Description)
End Function

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext