VBA：For循环中更新最后一行的正确位置排查

问题说明 #

将用户窗体的ComboBox和TextBox值输出到工作表时，第二行及以下控件内容无法更新至工作表。经排查，问题在于原代码未按控件组（对应工作表的行）来更新最后一行索引lRow，导致所有控件内容都被写入同一行。需求是：用户窗体中同一行的控件（如ComboBox3、ComboBox13、TextBox16、TextBox26）需输出到工作表的同一行；且不同页面的控件组数量不同——页面3每组4个控件，页面4、5每组5个控件，此前尝试用变量i实现行递增未生效。

原代码问题分析 #

原代码通过For Each Ctrl In UserForm1.Controls逐个遍历控件，没有按控件组（行）的逻辑处理，导致lRow无法在合适的时机递增，所有控件内容都覆盖到同一行，无法生成多行数据。

解决方案 #

改为按控件组（对应工作表的每一行）批量处理，每个循环对应工作表的一行，处理完一组后立即递增lRow，同时针对不同页面的控件数量差异分别编写处理逻辑。

修改后的代码 #

Dim Nws As Worksheet, tb1 As ListObject
Dim lRow As Long
Dim i As Long
Dim ctrlLoc As MSForms.ComboBox, ctrlWork As MSForms.ComboBox
Dim ctrlWorkSub As MSForms.ComboBox, ctrlStaff As MSForms.TextBox
Dim ctrlNum As MSForms.TextBox
Dim strW As String

Set Nws = ThisWorkbook.Worksheets("Fill")
Set tb1 = Nws.ListObjects("Output")

' 获取Output列表最后一行的下一行作为起始行号
With tb1.ListColumns(2).Range
    lRow = .Find(What:="*", After:=.Cells(1), SearchDirection:=xlPrevious).Row + 1
End With

' 处理页面3的10组控件（每组4个）
For i = 3 To 12
    Set ctrlLoc = UserForm1.Controls("ComboBox" & i)
    Set ctrlWork = UserForm1.Controls("ComboBox" & (i + 10))
    Set ctrlStaff = UserForm1.Controls("TextBox" & (i + 13))
    Set ctrlNum = UserForm1.Controls("TextBox" & (i + 23))
    
    ' 组内任一控件非空则写入该行
    If ctrlLoc.Value <> "" Or ctrlWork.Value <> "" Or ctrlStaff.Value <> "" Or ctrlNum.Value <> "" Then
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 1).Value = ctrlLoc.Value
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 2).Value = ctrlWork.Value
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 3).Value = ctrlStaff.Value
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 4).Value = ctrlNum.Value
        
        lRow = lRow + 1 ' 处理完一组，行号递增
    End If
Next i

' 处理页面4的10组控件（每组5个，Work Type由两个ComboBox组合）
For i = 23 To 32
    Set ctrlLoc = UserForm1.Controls("ComboBox" & i)
    Set ctrlWork = UserForm1.Controls("ComboBox" & (i + 10))
    Set ctrlWorkSub = UserForm1.Controls("ComboBox" & (i + 20))
    Set ctrlStaff = UserForm1.Controls("TextBox" & (i + 13))
    Set ctrlNum = UserForm1.Controls("TextBox" & (i + 23))
    
    If ctrlLoc.Value <> "" Or ctrlWork.Value <> "" Or ctrlWorkSub.Value <> "" Or ctrlStaff.Value <> "" Or ctrlNum.Value <> "" Then
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 1).Value = ctrlLoc.Value
        strW = "4-" & ctrlWork.Value & ctrlWorkSub.Value
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 2).Value = strW
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 3).Value = ctrlStaff.Value
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 4).Value = ctrlNum.Value
        
        lRow = lRow + 1
    End If
Next i

' 处理页面5的10组控件（逻辑同页面4）
For i = 53 To 62
    Set ctrlLoc = UserForm1.Controls("ComboBox" & i)
    Set ctrlWork = UserForm1.Controls("ComboBox" & (i + 10))
    Set ctrlWorkSub = UserForm1.Controls("ComboBox" & (i + 20))
    Set ctrlStaff = UserForm1.Controls("TextBox" & (i + 3))
    Set ctrlNum = UserForm1.Controls("TextBox" & (i + 13))
    
    If ctrlLoc.Value <> "" Or ctrlWork.Value <> "" Or ctrlWorkSub.Value <> "" Or ctrlStaff.Value <> "" Or ctrlNum.Value <> "" Then
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 1).Value = ctrlLoc.Value
        strW = "5-" & ctrlWork.Value & ctrlWorkSub.Value
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 2).Value = strW
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 3).Value = ctrlStaff.Value
        tb1.DataBodyRange.Cells(lRow - tb1.HeaderRowRange.Row, 4).Value = ctrlNum.Value
        
        lRow = lRow + 1
    End If
Next i

' 释放对象
Set ctrlLoc = Nothing
Set ctrlWork = Nothing
Set ctrlWorkSub = Nothing
Set ctrlStaff = Nothing
Set ctrlNum = Nothing
Set Nws = Nothing
Set tb1 = Nothing

关键修改点 #

1. 按组处理而非逐个控件遍历：每个循环对应工作表的一行，确保同一组控件写入同一行；
2. 及时递增行号：每组控件处理完成后立即执行lRow = lRow + 1，保证下一组写入下一行；
3. 适配不同页面的控件数量：针对页面3（4个控件/组）和页面4、5（5个控件/组）分别编写循环，逻辑清晰；
4. 安全操作表格区域：使用tb1.DataBodyRange直接操作ListObject，避免因表头行号变化导致的定位错误；
5. 增加非空判断：仅当组内至少有一个控件有值时才写入行，避免生成空行。

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