get letter from "AX"

Posted on 2012-08-17
Last Modified: 2012-08-20
I am trying to read from Excel file starting from column "AX" up to column "CU".  This is on the header only.
My logic is not working right.        
Dim j As Integer = 0

        For j = 1 To 50
            Dim firstalpha As Integer = j / 26
            Dim secondalpha As Integer = (j - 1) Mod 26
            Dim letter As String = Convert.ToChar(65 + secondalpha)
            If firstalpha > 0 Then
                letter = Convert.ToChar(64 + firstalpha) + letter
            End If

Question by:VBdotnet2005
    LVL 38

    Accepted Solution

    First of all, how about using .Cells(Row, Column) instead of using the alphabetic column name.  This would allow you to reference the column by number and avoid the awkward translation code.

    Secondly, the logic to do the translation looks like this:
    For j = 49 To 98
    	Dim firstalpha As Integer = CInt(j \ 26)
    	Dim secondalpha As Integer = j Mod 26
    	Dim letter As String = Convert.ToChar(65 + secondalpha)
    	If firstalpha > 0 Then
    		letter = Convert.ToChar(64 + firstalpha) + letter
    	End If

    Open in new window

    It's not that far from what you had, but uses an offset to get the right column number and integer division.  There are other ways to do it as well.
    LVL 69

    Expert Comment

    by:Éric Moreau
    I have this very old code that used to work:

    '    Public Shared Function NextNomColumn(ByVal pstrCol As String) As String
    '        If pstrCol Is Nothing Then
    '            Throw New ArgumentNullException("pstrCol")
    '        End If

    '        Dim strFirst As String
    '        Dim strLast As String

    '        If pstrCol.Length = 1 Then
    '            strFirst = ""
    '            strLast = pstrCol
    '        Else
    '            strFirst = pstrCol.Substring(0, 1)
    '            strLast = pstrCol.Substring(1, 1)
    '        End If

    '        If strLast = "Z" Then
    '            If strFirst = "" Then
    '                strFirst = "A"
    '            Else
    '                'strFirst = Chr$(Asc(strFirst) + 1)
    '                strFirst = Convert.ToChar(Convert.ToInt32(Convert.ToChar(strFirst)) + 1)
    '            End If
    '            strLast = "A"
    '        Else
    '            'strLast = Chr$(Asc(strLast) + 1)
    '            strLast = Convert.ToChar(Convert.ToInt32(Convert.ToChar(strLast)) + 1)
    '        End If

    '        NextNomColumn = strFirst & strLast
    '    End Function

    Author Comment

    That works for me. Thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    This video discusses moving either the default database or any database to a new volume.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now