Excel 代码将每个单元格的字符数限制为 6 个

Question

希望有人能解答我。

我正在尝试为 Excel 生成代码，该代码按以下顺序产生 T5A0A0 和 T6Z9Z9 之间的所有可用字母数字结果：字母、数字、字母、数字、字母、数字。

该代码有效：

Sub GenerateOptions()
    Dim row As Long
    Dim col As Long
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim l As Integer
    Dim m As Integer
    Dim n As Integer
    Dim cellValue As String
    
    ' Set starting row and column
    row = 1
    col = 1
    
    ' Loop through each possible combination
    For i = 5 To 6
        For j = Asc("A") To Asc("Z")
            For k = 0 To 9
                For l = Asc("A") To Asc("Z")
                    For m = 0 To 9
                        For n = Asc("A") To Asc("Z")
                            ' Combine letters and numbers into a string
                            cellValue = "T" & i & Chr(j) & k & Chr(l) & m & Chr(n)
                            ' Check if current combination is within range and has 6 characters or less
                            If cellValue >= "T5A0A0" And cellValue <= "T6Z9Z9" Then
                                ' Write string to current cell and move to next row
                                Cells(row, col).Value = cellValue
                                row = row + 1
                            End If
                        Next n
                    Next m
                Next l
            Next k
        Next j
    Next i
End Sub

问题：

上面列出的代码产生的结果包含 >6 个字符。使列表无限。当我尝试添加 IF 语句“Len(cellValue) <= 6”时，代码中断。我尝试重新排列“Len()”，使其位于 IF 语句的开头：

If Len(cellValue) <= 6 And cellValue >= "T5A0A0" And cellValue <= "T6Z9Z9" 

最后：

If cellValue >= "T5A0A0" And cellValue <= "T6Z9Z9" And Len(cellValue) <= 6

我还嵌套了 If 语句：

If cellValue >= "T5A0A0" And cellValue <= "T6Z9Z9" Then
    If Len(cellValue) <= 6 

我现在不太确定还能尝试什么。有什么建议吗？

Answer 1

由于以下代码行，您发布的代码永远不会产生包含 6 个字符的字符串：

 cellValue = "T" & i & Chr(j) & k & Chr(l) & m & Chr(n)

您正在连接 7 个变量。我建议：

1. 您将上面的行更改为cellValue = "T" & i & Chr(j) & k & Chr(l) & m
2. 你删除使用n

循环使用m如下：

   For m = 0 To 9
       
       ' Combine letters and numbers into a string
       cellValue = "T" & i & Chr(j) & k & Chr(l) & m '& Chr(n)
       ' Check if current combination is within range and has 6 characters or less
       If cellValue >= "T5A0A0" And cellValue <= "T6Z9Z9" Then
           'If cellValue.Length = 6 Then
           ' Write string to current cell and move to next row
           Cells(row, col).Value = cellValue
           row = row + 1
       End If
   Next m

Answer 2

该代码有效，但在我的 PC 上需要 2 分钟才能完成，尝试了几种变体，但找不到解决方法：

Option Explicit

Sub GenerateAlphaNumericOutcomes()
Application.ScreenUpdating = False
Dim StartTime As Double
Dim SecondsElapsed As Double
    Dim firstChar As Integer
    Dim secondChar As Integer
    Dim thirdChar As Integer
    Dim fourthChar As Integer
    Dim fifthChar As Integer
    Dim sixthChar As Integer
    
    Dim outcome As String
      StartTime = Timer
    For firstChar = Asc("T") To Asc("U") - 1
        For secondChar = 5 To 6
            For thirdChar = Asc("A") To Asc("Z")
                For fourthChar = 0 To 9
                    For fifthChar = Asc("A") To Asc("Z")
                        For sixthChar = 0 To 9
                            outcome = Chr(firstChar) & CStr(secondChar) & Chr(thirdChar) & CStr(fourthChar) & Chr(fifthChar) & CStr(sixthChar)
                            If outcome >= "T5A0A0" And outcome <= "T6Z9Z9" Then
                                Sheet1.Cells(Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1) = outcome
                            End If
                        Next sixthChar
                    Next fifthChar
                Next fourthChar
            Next thirdChar
        Next secondChar
    Next firstChar
    SecondsElapsed = Round(Timer - StartTime, 2)
Application.ScreenUpdating = True
  MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

enter code here

Answer 3

看到没有 VBA 标签，而且这是一个有趣的循环，所以我考虑了使用公式来实现它的方法，并想出了这些可以连接在一起的单独列：

A 列是硬编码的 T。

B 列的公式为：

=MOD(ROUNDDOWN((ROW()-2)/67600,0),10)+5

C 列用途

=CHAR(MOD(ROUNDUP((ROW()-1)/2600,0)-1,26)+65)

D列是：

=MOD(ROUNDDOWN((ROW()-2)/260,0),10)

E 列为：

=CHAR(MOD(ROUNDUP((ROW()-1)/10,0)-1,26)+65)

F 列为：

=MOD(ROW()-2,10)

那么 G 列就是前面几列的 CONCAT。

这需要约 1 秒的时间从第 2 行向下填充到 135,201 并计算。