[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

get letter from "AX"

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
VBdotnet2005
Asked:
VBdotnet2005
1 Solution
 
PaulHewsCommented:
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

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.
0
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
VBdotnet2005Author Commented:
PaulHews
That works for me. Thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now