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
   Select Case iColNo
      Case 0: Col_Letter = Chr(90)
      Case Is <= 26: Col_Letter = Chr(iColNo + 64)
      Case Else
         If iColNo Mod 26 = 0 Then
            Col_Letter = Chr(64 + (iColNo / 26) - 1) & Col_Letter(iColNo Mod 26)
            Exit Function
         End If
         sstartletter = Chr(Int(iColNo / 26) + 64)
         Col_Letter = sstartletter & Col_Letter(iColNo Mod 26)
   End Select
   If gbDEBUG = False Then Exit Function
AnError:
   Call Error_Handle("Col_Letter", msMODULENAME, 1, _
        "return the corresponding letter for the column number " & _
        "'" & iColNo & "'.")
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
   If gbDEBUG = False Then Exit Function
AnError:
   Call Error_Handle("Col_Number", msMODULENAME, _
        "return the corresponding number for the column letter " & _
        "'" & sColChar & "'.")
End Function



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