Excel VBA: how to convert column number to letters

 

You're working with Excel in VBA is you're trying to convert the column number to letters but you don't know how to go on? Don't worry, keep reading and I'll explain how to do it.

The operation is very simple, all you have to do is call the function indicated below: ConvertToLetter passing as a parameter the number of the column you already know.

If you don't know where to start, go on Development in Excel (in the top menu) and click on Record Macro and in the small window that appears click on OK.

Immediately after click on the top Stop Recording and press the icon VISUAL BASIC.

In the window that opens, on the left click on Module 1 and add in the right window inside Sub Macro1 () the code to call the ConverToLetter function (in practice copy the code below).

Coltesto = ConvertToLetter (NumCol)  'NumCol is the number of the column you are passing through.

Add immediately after this command:

Msgbox Coltesto  'you will need it to see the column letter

After End Sub di Macro1() copy and paste the function below:

Function ConvertToLetter(byval iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol – (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

You will need it to convert the column number to letters.

Good Luck!