Column number conversion to Excel Alphabet Columns

Asked By 10 points N/A Posted on -

Column number conversion to Excel Alphabet Columns

I want to fill with data each cells horizontally through VBA. The data is consisting of multiple rows and columns and its columns are ranging from 50 to 500 columns.

The range object uses the array of Cell Address which is in this format [column (alphabet)] [rows (numeric)]. To define this in my program I need to convert the numeric column, like for instance 27 which is AA on the Excel Column. It should be increasing like how the Excel column was arranged. A VBA functions is what I need. 

Thanks for the help.

Answered By 0 points N/A #101730

Column number conversion to Excel Alphabet Columns


Hope this sample would answer your problem. Though this would only convert integers that are passed into their equivalent alphanumeric text character. And also the appearance of the column or the row heading on the physical worksheet is still the same.

This is the VBA method.

Function ConvertToLetter(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

Or you can use this method:

  1. Divide the column number by 27, and then put the resulting integer in the variable "i".
  2. Subtract the column number from "i" multiplied by 26, and then put the result in the variable "j".
  3. Convert the integer values into their corresponding alphabetical characters, "i" and "j" will range from 0 to 26 respectively.

Hope this helps.

Login/Register to Answer

Related Questions