Use VBA to convert excel column number to string (label) and reverse function

[mlibeson]

Function ConvertToLetter(iCol As Integer) As String
   Dim i, j As Integer
   Dim sCol As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer

   iRemainder = Int(iCol Mod 26)
   If iRemainder = 0 Then
      iRemainder = 26
   End If
   
   sCol = Chr(iRemainder + 64)
   If ((iCol - iRemainder) > 0) And (iCol > 26) Then
      sCol = ConvertToLetter(Int((iCol - iRemainder) / 26)) & sCol
   End If
   
   ConvertToLetter = sCol
End Function

Function ConvertToInteger(cCol As String) As Integer
   Dim iMid, i, j As Integer

   cCol = Trim(cCol)
   If Len(cCol) > 1 And Len(cCol) < 5 Then
      iMid = 0
      j = (Len(cCol) - 1)
      For i = 1 To (Len(cCol) - 1)
         iMid = iMid + (ConvertToInteger(Mid(cCol, i, 1)) * 26 ^ j)
         j = j - 1
      Next i
      ConvertToInteger = iMid + (Asc(UCase(Right(cCol, 1))) - 64)
   ElseIf Len(cCol) = 1 And Len(cCol) < 5 Then
      ConvertToInteger = Asc(UCase(cCol)) - 64
   Else
      ConvertToInteger = 0
   End If
End Function

Michael Libeson

 

[SkipVought]

Is there a question there?

Function ColNbr(rng As Range) As Integer
    ColNbr = rng.Column
End Function

Function ColLtr(rng As Range) As String
    ColLtr = Left(Cells(1, rng.Column).Address(False, False), Len(Cells(1, rng.Column).Address(False, False)) - 1)
End Function

Function Col2Nbr(sCol As String) As Integer
    Col2Nbr = ColNbr(Cells(1, sCol))
End Function

Function Col2Ltr(iCol As Integer) As String
    Col2Ltr = ColLtr(Cells(1, iCol))
End Function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mlibeson]

Just sharing. I had problems with using the address function as it was not available, so I had to come up with another way to convert back and forth between the letter columns and number columns. I tried what others recommended including what was posted on a Microsoft web page, but there were issues with some of the conversions. The one provided appears to work. If anyone runs into any issues using it, please let me know.

Michael Libeson

 

[SkipVought]

Huh? Never rralized that.

In what version of Excel is Address not available?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]