Excel VBA UserForm添加按钮功能异常：数据写入与填充问题求助

原代码核心问题 #

1. 行号定位错误：使用CountA(D:D)计算行号，若D列存在空白单元格会导致行号偏移；且未跟踪当前处理的目标行，导致输入始终写入最后一行。
2. 填充文本框逻辑混乱：错误调用Me.Controls.Value（控件集合无法直接取Value）、Find逻辑无效、循环赋值逻辑错误，无法正确加载下一行B/C/D列数据。

修正后的完整代码 #

首先在UserForm模块顶部声明模块级变量，用于跟踪当前处理的行号：

Dim currentRow As Long

然后添加UserForm初始化事件，加载初始行数据：

Private Sub UserForm_Initialize()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    currentRow = 2 ' 初始处理第2行
    LoadRowData currentRow ' 加载B2/C2/D2数据到文本框
End Sub

封装加载行数据的辅助过程：

Private Sub LoadRowData(rowNum As Long)
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    
    ' 读取B/C/D列到对应文本框
    Me.TextBox1.Value = sh.Cells(rowNum, 2).Value ' B列
    Me.TextBox2.Value = sh.Cells(rowNum, 3).Value ' C列
    Me.TextBox3.Value = sh.Cells(rowNum, 4).Value ' D列
    
    ' 检测是否无有效数据
    If Trim(Me.TextBox1.Value) = "" And Trim(Me.TextBox2.Value) = "" And Trim(Me.TextBox3.Value) = "" Then
        Me.TextBox1.Value = ""
        Me.TextBox2.Value = ""
        Me.TextBox3.Value = ""
        MsgBox "已无更多待处理行"
    End If
End Sub

封装清空控件的辅助过程：

Private Sub ClearAllControls()
    ' 清空组合框
    Me.ComboBox1.Value = ""
    Me.ComboBox2.Value = ""
    Me.ComboBox3.Value = ""
    ' 清空文本框
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.TextBox6.Value = ""
    Me.TextBox7.Value = ""
    Me.TextBox8.Value = ""
End Sub

最后修改"添加"按钮的点击事件：

Private Sub CommandButton2_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    
    ' 1. 将输入写入当前行的E-L列
    sh.Cells(currentRow, 5).Value = Me.TextBox5.Value ' E列
    sh.Cells(currentRow, 6).Value = Me.ComboBox1.Value ' F列
    sh.Cells(currentRow, 7).Value = Me.ComboBox2.Value ' G列
    sh.Cells(currentRow, 8).Value = Me.ComboBox3.Value ' H列
    sh.Cells(currentRow, 9).Value = Me.TextBox4.Value ' I列
    sh.Cells(currentRow, 10).Value = Me.TextBox8.Value ' J列
    sh.Cells(currentRow, 11).Value = Me.TextBox6.Value ' K列
    sh.Cells(currentRow, 12).Value = Me.TextBox7.Value ' L列
    
    ' 2. 清空所有控件
    ClearAllControls
    
    ' 3. 加载下一行的B/C/D列数据
    currentRow = currentRow + 1
    LoadRowData currentRow
End Sub

关键改进说明 #

• 模块级变量currentRow：精准跟踪当前处理的行号，确保输入写入对应行，而非随机的"最后一行"。
• 拆分辅助过程：将加载数据、清空控件的逻辑单独封装，避免代码冗余，便于维护和调试。
• 直接单元格定位：放弃不可靠的CountA和Find，直接用行号+列索引定位单元格，逻辑更稳定。

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