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

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