VBA按ComboBox选中客户在数据库末行插入员工信息问题求解

你制作的Excel员工信息录入窗体，已实现初始化阶段ComboBox加载客户名称列表的功能，但提交按钮写入新员工信息的逻辑失效，无法将录入内容写入Database工作表对应客户列的空行中。
原实现代码如下：

Private Sub Userform_Initialize()
Dim i As Integer
Worksheets("Database").Select
For i = 1 To 4000
ComboBox1.AddItem Cells(1, i)
Next
End Sub
Private Sub CommandButton1_Click()
Dim cell As Range
Dim var As String
Dim rw As Integer
Dim field_search As Range
Worksheets("Database").Select
Set field_search = Range("A1", Range("A1").End(xlToRight))
For Each cell In field_search
If cell.Value = ComboBox1.Value Then
cell.EntireColumn.Select
rw = cell.Cells.Find(What:=" ", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
cell.Cells(2, rw).Value = TextBox4 + " " + TextBox3
cell.Cells(3, rw).Value = TextBox2 + " " + TextBox5
cell.Cells(4, rw).Value = TextBox6 + " " + TextBox7
End If
Next
End Sub

• 初始化逻辑硬编码遍历1~4000列，会将大量空表头加载到ComboBox下拉列表，冗余且容易误选空值
• 定位列内最后一行数据时，Find方法搜索目标为空格" "，无法识别正常的非空员工数据，返回的行号完全错误
• 单元格引用逻辑颠倒：cell是第1行的客户表头单元格，cell.Cells(行参数, 列参数)是基于cell的相对偏移引用，原代码将计算得到的行号rw传入列参数位置，写入位置完全偏离目标列
• 字符串拼接使用+运算符，存在数值类型自动转换的异常风险
• 无异常兜底逻辑：未选择客户、客户列不存在、目标列无历史数据时会直接触发运行时错误

直接替换原有窗体代码即可，保留原有文本框的对应写入规则，新增了空值校验和录入成功提示：

Private Sub Userform_Initialize()
    Dim i As Long, lastHeaderCol As Long
    With Worksheets("Database")
        ' 自动定位表头行最后一个非空列，避免加载空值
        lastHeaderCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For i = 1 To lastHeaderCol
            If .Cells(1, i).Value <> "" Then
                ComboBox1.AddItem .Cells(1, i).Value
            End If
        Next
    End With
End Sub

Private Sub CommandButton1_Click()
    Dim targetCol As Long, lastDataRow As Long
    Dim ws As Worksheet
    
    ' 校验是否已选择客户
    If ComboBox1.Value = "" Then
        MsgBox "请先选择对应客户名称", vbExclamation
        Exit Sub
    End If
    
    Set ws = Worksheets("Database")
    ' 精确匹配选中客户所在列号
    On Error Resume Next
    targetCol = ws.Rows(1).Find(What:=ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Column
    On Error GoTo 0
    If targetCol = 0 Then
        MsgBox "未查询到对应客户列，请检查选择", vbExclamation
        Exit Sub
    End If
    
    ' 定位目标列最后一条已有数据的行号
    lastDataRow = ws.Cells(ws.Rows.Count, targetCol).End(xlUp).Row
    
    ' 按原有文本框对应规则写入数据，用&做字符串拼接规避类型错误
    ws.Cells(lastDataRow + 1, targetCol).Value = TextBox4.Value & " " & TextBox3.Value
    ws.Cells(lastDataRow + 2, targetCol).Value = TextBox2.Value & " " & TextBox5.Value
    ws.Cells(lastDataRow + 3, targetCol).Value = TextBox6.Value & " " & TextBox7.Value
    
    ' 清空文本框方便下次录入
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    MsgBox "员工信息录入完成", vbInformation
End Sub

• 代码默认Database工作表第1行为客户名称表头，单个员工的3项信息在对应客户列内按相邻3行存放，和你原有逻辑的存储结构完全一致
• 不需要手动选中工作表/单元格，所有操作直接后台定位，不会触发界面跳转
• 如果后续调整存储结构，只需要修改写入部分的行号偏移量即可

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