Excel Userform根据ComboBox值填充TextBox时VLookup报1004错误问询

错误原因排查 #

• 最常见的原因是WorksheetFunction.VLookup在未找到匹配值时会直接抛出运行时错误1004，没有内置容错机制
• 其次是数据类型不匹配：ComboBox返回的值默认是字符串类型，如果Data表A列的唯一ID是数值格式，两者比对会判定为不匹配触发报错
• 另外如果A列存在空值、或者硬编码的查找范围超出实际数据区域，也可能触发该错误

修复后的完整实现代码 #

Private Sub UserForm_Initialize()
    Dim dataWs As Worksheet
    Dim lastRow As Long
    Set dataWs = ThisWorkbook.Worksheets("Data")
    ' 动态获取A列最后一行有效数据，避免硬编码范围
    lastRow = dataWs.Cells(dataWs.Rows.Count, "A").End(xlUp).Row
    ' 绑定ComboBox列表
    ComboBox1.List = dataWs.Range("A2:A" & lastRow).Value
End Sub

Private Sub ComboBox1_Change()
    Dim dataWs As Worksheet
    Dim lookupRng As Range
    Dim matchRow As Variant
    Dim searchId As Variant
    
    Set dataWs = ThisWorkbook.Worksheets("Data")
    Set lookupRng = dataWs.Range("A2:N" & dataWs.Cells(dataWs.Rows.Count, "A").End(xlUp).Row)
    
    ' 自动适配数值/字符串格式的ID，解决类型不匹配问题
    If IsNumeric(ComboBox1.Value) Then
        searchId = Val(ComboBox1.Value)
    Else
        searchId = ComboBox1.Value
    End If
    
    ' 用Match函数先定位匹配行，支持容错处理
    matchRow = Application.Match(searchId, lookupRng.Columns(1), 0)
    
    ' 未找到匹配值时清空所有文本框，可根据需求调整逻辑
    If IsError(matchRow) Then
        TextBox1.Value = ""
        TextBox2.Value = ""
        TextBox3.Value = ""
        TextBox4.Value = ""
        TextBox5.Value = ""
        TextBox6.Value = ""
        TextBox7.Value = ""
        Exit Sub
    End If
    
    ' 按需求对应列填充文本框，可根据实际控件编号调整对应关系
    With lookupRng
        TextBox1.Value = .Cells(matchRow, 2).Value ' 对应B列
        TextBox2.Value = .Cells(matchRow, 3).Value ' 对应C列
        TextBox3.Value = .Cells(matchRow, 5).Value ' 对应E列
        TextBox4.Value = .Cells(matchRow, 6).Value ' 对应F列
        TextBox5.Value = .Cells(matchRow, 9).Value ' 对应I列
        TextBox6.Value = .Cells(matchRow, 10).Value ' 对应J列
        TextBox7.Value = .Cells(matchRow, 11).Value ' 对应K列
    End With
End Sub

注意事项 #

• 请根据你实际的TextBox控件编号调整上述代码中赋值的对应关系，避免填充错位
• 如果唯一ID是带前导零的字符串格式，可删除ID类型转换的逻辑，直接使用searchId = ComboBox1.Value即可
• 代码采用动态行号读取数据，不需要手动调整数据范围，新增数据时会自动适配

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