VBA通过用户窗体新增记录提示运行时错误1004如何解决

错误原因 #

• 核心触发点：代码中所有End(x1Down)存在拼写错误：VBA内置的向下定位常量为xlDown（x后接小写字母l），你写的是x后接数字1，VBA无法识别该非法常量，直接抛出错误。
• 潜在报错点：
  • 若A列无有效数据，从A1向下定位会直接跳转到工作表最大行（1048576行），再执行Offset(1,0)会超出工作表行范围，触发1004错误。
  • 逐列单独调用End(xlDown)定位行号，如果某列存在空单元格，会导致各字段写入的行号错位，数据不匹配。
  • 先激活工作表再操作Range的写法不稳定，若代码运行时焦点在其他工作表（比如窗体显示时激活了别的表），会出现Range引用错误。

修复后完整代码 #

Private Sub CommandButton1_Click()
    Dim targetSht As Worksheet
    Dim writeRow As Long
    ' 绑定目标工作表，无需激活即可操作
    Set targetSht = ThisWorkbook.Worksheets("Sheet7")
    ' 从表底向上定位A列最后一个非空单元格，计算下一个待写入行号，兼容空表场景
    writeRow = targetSht.Cells(targetSht.Rows.Count, "A").End(xlUp).Row + 1
    
    ' 写入自增序号
    targetSht.Cells(writeRow, "A").Value = targetSht.Cells(writeRow - 1, "A").Value + 1
    ' 逐列写入窗体控件值
    targetSht.Cells(writeRow, "B").Value = TextBox27.Value
    targetSht.Cells(writeRow, "C").Value = ComboBox8.Value
    targetSht.Cells(writeRow, "D").Value = ComboBox3.Value
    targetSht.Cells(writeRow, "E").Value = TextBox50.Value
    targetSht.Cells(writeRow, "F").Value = ComboBox1.Value
    targetSht.Cells(writeRow, "G").Value = TextBox48.Value
    targetSht.Cells(writeRow, "H").Value = TextBox47.Value
    targetSht.Cells(writeRow, "I").Value = TextBox46.Value
    targetSht.Cells(writeRow, "J").Value = ComboBox7.Value
    targetSht.Cells(writeRow, "K").Value = TextBox44.Value
    targetSht.Cells(writeRow, "L").Value = TextBox43.Value
    targetSht.Cells(writeRow, "M").Value = TextBox42.Value
    targetSht.Cells(writeRow, "N").Value = TextBox41.Value
    targetSht.Cells(writeRow, "O").Value = TextBox40.Value
    targetSht.Cells(writeRow, "P").Value = TextBox39.Value
    targetSht.Cells(writeRow, "Q").Value = TextBox38.Value
    targetSht.Cells(writeRow, "R").Value = TextBox37.Value
    targetSht.Cells(writeRow, "S").Value = TextBox36.Value
    targetSht.Cells(writeRow, "T").Value = TextBox35.Value
    targetSht.Cells(writeRow, "U").Value = TextBox34.Value
    targetSht.Cells(writeRow, "V").Value = TextBox28.Value
    targetSht.Cells(writeRow, "W").Value = TextBox32.Value
    targetSht.Cells(writeRow, "X").Value = TextBox31.Value
End Sub

修复说明 #

• 修正常量拼写错误，替换所有非法的x1Down为标准常量xlDown。
• 改用VBA通用的「从表底向上定位」方式找最后一行，避免空表场景下越界报错。
• 提前统一计算待写入行号，所有字段写入同一行，彻底避免列空值导致的行错位问题。
• 直接通过工作表对象引用单元格，移除不必要的Activate激活操作，提升代码稳定性和运行效率。

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