Column number conversion to Excel Alphabet Columns

Asked By Yolly 10 points N/A Posted on - 08/08/2011

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.

• Status: Open
• Question Views: 1578
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: MS Excel

Answered By hillamanda 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.

About hillamanda

Questions
0

Answers
6

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 09/25/2011
• Question Category: MS Excel