如何合并循环实现UserForm文本框值批量写入Excel工作表

Looking at your code, it's clear you're repeating the exact same pattern for each year's set of TextBoxes and worksheet rows. We can cut through all that redundancy by spotting the consistent mapping rules between your TextBoxes and worksheet rows, then wrapping that logic in clean, reusable loops.

Key Patterns We Can Leverage: #

• Each year uses 12 TextBoxes: odd-numbered ones map to the first 6 rows of the year's worksheet block, even-numbered ones map to the next 6 rows.
• Every year's data takes up 12 consecutive worksheet rows (starting at row 1, 13, 25, etc.—increments of 12).
• Each year's TextBox sequence starts at 1, 13, 25, etc.—also increments of 12, matching the row blocks.

Optimized Code (Two Inner Loops) #

This version keeps the logic straightforward while eliminating all redundant code:

Private Sub Inserir_Click()
    Dim ws As Worksheet
    Dim blockNum As Integer ' 0 = 89, 1 = 90, ..., 6 = 95 (7 total year blocks)
    Dim rowOffset As Integer ' Offset within the 12-row block (0 to 5)
    Dim startRow As Integer
    Dim startTextBox As Integer
    Dim textBoxNum As Integer
    
    Set ws = Worksheets("Planilha1")
    
    ' Loop through each year's data block
    For blockNum = 0 To 6
        startRow = 1 + blockNum * 12 ' Starting row for the current year's 12-row block
        startTextBox = 1 + blockNum * 12 ' Starting TextBox number for the current year
        
        ' Handle first 6 rows (odd-numbered TextBoxes: 1,3,5...11,13,15...)
        For rowOffset = 0 To 5
            textBoxNum = startTextBox + (rowOffset * 2)
            ws.Cells(startRow + rowOffset, 1).Value = Me.Controls("Textbox" & textBoxNum).Value
        Next rowOffset
        
        ' Handle next 6 rows (even-numbered TextBoxes: 2,4,6...12,14,16...)
        For rowOffset = 0 To 5
            textBoxNum = startTextBox + (rowOffset * 2) + 1
            ws.Cells(startRow + 6 + rowOffset, 1).Value = Me.Controls("Textbox" & textBoxNum).Value
        Next rowOffset
    Next blockNum
End Sub

How This Works: #

1. Outer Loop (blockNum): Iterates over each year's data block. We use 0 to 6 to cover all 7 years (89 to 95).
2. Calculate Start Points: For each block, we compute the first row and first TextBox number by multiplying the block index by 12 (since each block uses 12 rows/TextBoxes) and adding 1.
3. Inner Loops:
   • The first inner loop handles odd-numbered TextBoxes: we multiply the row offset by 2 to skip even numbers, mapping to the first 6 rows of the block.
   • The second inner loop handles even-numbered TextBoxes: same as the first, but we add an extra 1 to shift to even values, mapping to rows 7-12 of the block.

Even More Streamlined Version (Single Inner Loop) #

If you prefer to combine the two inner loops into one, we can check the row position within the block to pick the right TextBox:

Private Sub Inserir_Click()
    Dim ws As Worksheet
    Dim blockNum As Integer ' 0 = 89, 1 = 90, ..., 6 = 95
    Dim rowInBlock As Integer ' 1 to 12 within each year's block
    Dim startRow As Integer
    Dim startTextBox As Integer
    Dim textBoxNum As Integer
    
    Set ws = Worksheets("Planilha1")
    
    For blockNum = 0 To 6
        startRow = 1 + blockNum * 12
        startTextBox = 1 + blockNum * 12
        
        For rowInBlock = 1 To 12
            If rowInBlock <= 6 Then
                ' First 6 rows: use odd-numbered TextBoxes
                textBoxNum = startTextBox + (rowInBlock - 1) * 2
            Else
                ' Last 6 rows: use even-numbered TextBoxes
                textBoxNum = startTextBox + (rowInBlock - 7) * 2 + 1
            End If
            ws.Cells(startRow + rowInBlock - 1, 1).Value = Me.Controls("Textbox" & textBoxNum).Value
        Next rowInBlock
    Next blockNum
End Sub

Both versions do exactly what your original code does, but with far less redundancy. If you ever need to add more years later, you just adjust the outer loop's upper limit—no need to copy-paste entire blocks of code.

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