Why is my code displaying an "@" symbol in place of a Z

Question

I am currently trying to write a function in VBA that when ran in excel will prompt a user to enter a number of rows and a number of columns. The function will then create a table in excel based on those inputs with the columns being labeled using letters (A-Z) and the rows being labeled using numbers.

I have been able to get the code to operate for the most part however the issue I am running into is once the column input gets greater than 26 (essentially what would be Z) getting the table to restart and display AA, AB, AC, etc. has been tricky. Right now, the code runs as intended but instead of displaying what should be "AZ" the code will print "B@". Same issue if the column input was large enough that "BZ" is needed, the code will print "C@" and then go back to the correct numbering system. How can i get my for loop to display this information correctly? Below is what I currently have set up for my code

Sub CreateTableWithLabelsAndRepeat()
Dim numRows As Integer
Dim numCols As Integer
Dim ws As Worksheet
Dim tblRange As Range
Dim tbl As ListObject
Dim i As Integer
Dim j As Integer

' Prompt user for number of rows and columns
numRows = InputBox("Enter the number of rows for the table:", "Table Rows")
numCols = InputBox("Enter the number of columns for the table:", "Table Columns")

' Validate input
If numRows <= 0 Or numCols <= 0 Then
    MsgBox "Invalid input. Please enter positive numbers.", vbExclamation
    Exit Sub
End If

' Set worksheet and range for the table
Set ws = ActiveSheet
Set tblRange = ws.Range("E5").Resize(numRows + 1, numCols + 1) ' +1 for header row and column. Table starts at E5 due to other data on excel sheet

' Clear existing content in the range
tblRange.Clear

' Label columns with letters
For i = 1 To numCols
    If i < 27 Then
        tblRange.Cells(1, i + 1).Value = Chr(64 + i) ' Column headers as letters (A, B, C, ...)
    Else
        tblRange.Cells(1, i + 1).Value = Chr(64 + Int(i / 26)) & Chr(64 + i - (Int(i / 26) * 26))
    End If
        
Next i

' Label rows with numbers
For j = 1 To numRows
    tblRange.Cells(j + 1, 1).Value = j ' Row labels as numbers (1, 2, 3, ...)
Next j

' Create the table
Set tbl = ws.ListObjects.Add(xlSrcRange, tblRange, , xlYes)
tbl.Name = "DynamicTable"
tbl.TableStyle = "TableStyleMedium2"

' Apply center alignment to all cells
tblRange.HorizontalAlignment = xlCenter
tblRange.VerticalAlignment = xlCenter

MsgBox "Table created successfully!", vbInformation

End Sub

Table Print Error showing 'B@' when is should be 'AZ'

Answer 1

Create Excel Table with Labels

Issues

• If the user enters a string, your code will fail because you cannot assign a string to an Integer variable.
  If the user enters too big numbers (for rows or columns), your code will fail.
  Improve the validation of the inputs by checking if valid numbers (integers) were entered.

• If the existing content is bigger than the new content, the result will be incorrect.
  Determine more accurately what to delete: you could delete the existing table but I opted to use the RefCurrentRegion function which references all adjacent data below and to the right of the top left cell. It allows you to keep the data above and to the left.

• The logic of determining the column strings is wrong.
  I used the GetColumnString function instead. So could you in your code, instead of the If statement:

  tblRange.Cells(1, i + 1).Value = GetColumnString(i)
  

• To increase efficiency, I used an array (Data) to build the labels and copy them to the sheet in one go.

The Code

Main

Sub CreateTableWithLabelsAndRepeat()
    
    ' Define constants.
    Const TABLE_NAME As String = "DynamicTable"
    Const TABLE_STYLE As String = "TableStyleMedium2"
    Const TABLE_TOP_LEFT_CELL_ADDRESS As String = "E5"
    Const TABLE_TOP_LEFT_CELL_TITLE As String = "Num"
    Dim HORIZONTAL_ALIGNMENT As XlHAlign: HORIZONTAL_ALIGNMENT = xlHAlignCenter
    Dim VERTICAL_ALIGNMENT As XlVAlign: VERTICAL_ALIGNMENT = xlVAlignCenter
    
    ' Reference the worksheet and the top left cell.
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim tlcell As Range: Set tlcell = ws.Range(TABLE_TOP_LEFT_CELL_ADDRESS)
    
    ' Prompt the user to input the number of rows.
    Dim MaxRows As Long: MaxRows = ws.Rows.Count - tlcell.Row
    Dim RowsInput As String: RowsInput = InputBox( _
        "Enter the number of rows for the table:", "Table Rows")
    
    ' Validate the number of rows.
    Dim RowsCount As Long, IsRowsInputValid As Boolean
    If IsNumeric(RowsInput) Then
        RowsCount = CLng(RowsInput)
        If RowsCount >= 1 And RowsCount <= MaxRows Then IsRowsInputValid = True
    End If
    If Not IsRowsInputValid Then
        MsgBox "Please enter a number between 1 and " & MaxRows & ".", _
            vbExclamation, "Table Rows"
        Exit Sub
    End If
        
    ' Prompt the user to input the number of columns.
    Dim MaxCols As Long: MaxCols = ws.Columns.Count - tlcell.Column
    Dim ColsInput As String: ColsInput = InputBox( _
        "Enter the number of columns for the table:", "Table Columns")
    
    ' Validate the number of columns.
    Dim ColsCount As Long, IsColsInputValid As Boolean
    If IsNumeric(ColsInput) Then
        ColsCount = CLng(ColsInput)
        If ColsCount >= 1 And ColsCount <= MaxCols Then IsColsInputValid = True
    End If
    If Not IsColsInputValid Then
        MsgBox "Please enter a number between 1 and " & MaxCols & ".", _
            vbExclamation, "Table Columns"
        Exit Sub
    End If
        
    Application.ScreenUpdating = False
            
    ' Clear existing content.
    RefCurrentRegion(tlcell).Clear
        
    ' Reference the table range.
    Dim tblRange As Range: Set tblRange = _
        tlcell.Resize(RowsCount + 1, ColsCount + 1) ' +1 for labels
    
    ' Populate the top left cell.
    tlcell.Value = TABLE_TOP_LEFT_CELL_TITLE
    
    Dim Data() As Variant, i As Long
    
    ' Label columns with letters.
    ReDim Data(1 To ColsCount)
    For i = 1 To ColsCount
        Data(i) = GetColumnString(i) ' Column labels as Excel columns (A,... XFD)
    Next i
    tlcell.Offset(, 1).Resize(, ColsCount).Value = Data
    
    ' Label rows with numbers.
    ReDim Data(1 To RowsCount, 1 To 1)
    For i = 1 To RowsCount
        Data(i, 1) = i ' Row labels as numbers (1, 2, 3, ...)
    Next i
    tlcell.Offset(1).Resize(RowsCount).Value = Data
    
    ' Create the table.
    Dim tbl As ListObject:
    Set tbl = ws.ListObjects.Add(xlSrcRange, tblRange, , xlYes)
    tbl.Name = TABLE_NAME
    tbl.TableStyle = TABLE_STYLE
    
    ' Apply alignment to all cells
    tblRange.HorizontalAlignment = HORIZONTAL_ALIGNMENT
    tblRange.VerticalAlignment = VERTICAL_ALIGNMENT
    
    Application.ScreenUpdating = True
    
    ' Inform.
    MsgBox "Table successfully created!", vbInformation, "Create Table"

End Sub

Help

Function RefCurrentRegion(ByVal firstCell As Range) As Range
    If firstCell Is Nothing Then Exit Function
    With firstCell.Cells(1).CurrentRegion
        Set RefCurrentRegion = firstCell.Resize(.Row + .Rows.Count _
            - firstCell.Row, .Column + .Columns.Count - firstCell.Column)
    End With
End Function

Function GetColumnString(ByVal ColumnNumber As Long) As String
    Dim Remainder As Long
    Do
        Remainder = (ColumnNumber - 1) Mod 26
        GetColumnString = Chr(Remainder + 65) & GetColumnString
        ColumnNumber = Int((ColumnNumber - Remainder) \ 26)
    Loop Until ColumnNumber = 0
End Function