VBA设置数据验证列表时反复触发1004错误求助

核心错误原因 #

你触发1004错误的本质是误解了xlValidateList类型数据验证的要求：

• 列表型数据验证（xlValidateList）的Formula1必须指向可提供多个可选值的数据源——要么是逗号分隔的静态文本（如"Option1,Option2"），要么是返回单元格区域/动态数组的公式。
• 你使用的XLookup（返回单个值）、Today()（返回单个日期）都是返回单个值的公式，完全不符合列表验证的数据源要求，直接触发参数错误。

代码中的具体问题 #

1. 公式语法错误：第一个代码片段里的FM变量最后缺少闭合括号，属于语法错误，会进一步加重问题：

   ' 原错误代码：末尾少了一个")"
   FM = "=XLookup(" & RA & ",KinderDropDown!$A$1#,KinderDropDown!$A$2:" & RA2 & ",""Kein Kind vorhanden"""
   ' 修正后：
   FM = "=XLookup(" & RA & ",KinderDropDown!$A$1#,KinderDropDown!$A$2:" & RA2 & ",""Kein Kind vorhanden"")"
   

2. 不必要的单元格激活：用Select和ActiveCell.Address完全多余，直接用Cells(TR,2).Address即可，避免激活单元格带来的不稳定。
3. 验证类型误用：如果是要限制单元格值为当前日期，应该用xlValidateDate类型，而不是列表类型。

修正后的代码示例 #

1. 动态列表验证（基于XLookup返回区域） #

如果你想通过XLookup动态匹配下拉列表的数据源，必须让公式返回单元格区域/动态数组，比如匹配后返回整列的可选值：

Dim targetCell As Range
Set targetCell = Cells(TR, 8) ' 目标单元格：第TR行第8列（H列）

' 清除原有验证（避免重复添加报错）
With targetCell.Validation
    If .Type <> xlValidateNone Then .Delete
End With

' 构建返回区域的公式：假设根据B列的值，匹配KinderDropDown表中对应的整列可选区域
Dim listFormula As String
listFormula = "=XLOOKUP(" & Cells(TR, 2).Address(External:=False) & ", KinderDropDown!$A$1#, KinderDropDown!$B$1#)"

' 添加列表验证
With targetCell.Validation
    .Add Type:=xlValidateList, _
         AlertStyle:=xlValidAlertStop, _
         Formula1:=listFormula
End With

2. 日期验证（替代Today()的错误用法） #

如果你的需求是限制单元格值为当前日期，改用日期类型验证：

With Range("H2").Validation
    .Delete
    .Add Type:=xlValidateDate, _
         AlertStyle:=xlValidAlertStop, _
         Operator:=xlEqual, _
         Formula1:="=Today()"
End With

总结 #

• xlValidateList只接受多选项数据源，单个值公式会直接报错；
• 验证类型要和需求匹配：列表用xlValidateList，日期限制用xlValidateDate；
• 避免使用Select/ActiveCell，直接操作Range对象更稳定。

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