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
   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


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