如何将VBA多段重复迭代合并为单循环简化批量处理代码

优化后单循环实现代码 #

你只需要在外层新增一层遍历Sheet1读取行的循环即可，无需重复编写逻辑块，完整代码如下：

Sub Decode_String()
    Dim Fault_Block(2000)
    Dim sheet1_start_row As Long, sheet1_end_row As Long
    Dim batch_offset As Long, i As Long, j As Long
    
    ' 配置Sheet1读取的行范围：从第2行到第100行
    sheet1_start_row = 2
    sheet1_end_row = 100
    ' 每个字符串对应20条结果，计算批次偏移量
    For i = sheet1_start_row To sheet1_end_row
        batch_offset = (i - sheet1_start_row) * 20
        For j = 1 To 20
            Fault_Block(batch_offset + j) = Mid(Sheet1.Cells(i, 3), (66 * j) - 65, 4)
            ' 写入Sheet2的B列，起始行从2开始计算
            Sheet2.Cells(batch_offset + j + 1, 2) = Fault_Block(batch_offset + j)
        Next
    Next
End Sub

逻辑说明 #

• 外层循环i控制Sheet1的读取行，遍历范围直接设置为2到100，覆盖所有待处理字符串
• batch_offset变量计算每个批次的结果在Sheet2中的偏移位置，自动对应每次写入的行号，无需手动调整每个循环的写入范围
• 如果不需要存储中间结果到Fault_Block数组，也可以直接省略数组赋值，直接把Mid的结果写入Sheet2单元格，进一步简化代码：

' 简化版（无中间数组）
Sub Decode_String_Simple()
    Dim i As Long, j As Long
    For i = 2 To 100
        For j = 1 To 20
            Sheet2.Cells((i - 2) * 20 + j + 1, 2) = Mid(Sheet1.Cells(i, 3), 66 * j - 65, 4)
        Next
    Next
End Sub

内容的提问来源于stack exchange，提问作者Cody