Solved

# get letter from "AX"

Posted on 2012-08-17
548 Views
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

Next
0
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
Debug.WriteLine(letter)
Next
``````

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

LVL 69

Expert Comment

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
0

Author Comment

PaulHews
That works for me. Thank you
0

## Featured Post

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.