使用数据验证下拉列表时触发Error 1004错误的求助

我来帮你排查这个头疼的问题，Error 1004在数据验证的VBA代码里，大多是公式格式或者引用逻辑出了问题，结合你的场景，主要有几个关键修正点：

问题根源拆解 #

1. VBA日期函数的语法错误：你在公式里用了分号;分隔DATE参数，但VBA里的函数参数分隔符必须是逗号,，这会直接导致公式解析失败。
2. 数据验证列表的引用逻辑问题：Excel数据验证的下拉列表需要直接指向单元格区域，而IF(...)返回的是命名区域的文本名称，不是实际的区域引用，必须用INDIRECT函数把文本转换成可识别的区域引用。
3. 跨工作表命名区域的识别问题：如果list_a和list_b是工作表级命名区域（仅在特定工作表可用），需要在引用时明确指定工作表；如果是工作簿级名称，也要确保VBA能正确识别到。

修正后的完整代码 #

Dim kolQ As Range
Set kolQ = Range("Q2:Q100") ' 替换成你实际需要设置的Q列区域

With kolQ.Validation
    .Delete ' 先清除目标区域原有数据验证规则
    ' 核心修正：用逗号分隔DATE参数，添加INDIRECT解析命名区域
    .Add Type:=xlValidateList, _
         Formula1:="=IF(AND($L2<DATE(2014,5,1),$M2>DATE(2015,4,30)),INDIRECT(""list_a""),INDIRECT(""list_b""))"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = "No valid status!"
    .InputMessage = ""
    .ErrorMessage = "Chose option from drop-down list!"
    .ShowInput = True
    .ShowError = True
End With

额外验证建议 #

• 确认命名区域的级别：打开「公式」选项卡→「名称管理器」，检查list_a和list_b的「范围」是否为工作簿；如果是工作表级，需要在INDIRECT里补充工作表名，比如INDIRECT("Sheet2!list_a")。
• 先手动测试公式：在Q2单元格输入=IF(AND($L2<DATE(2014,5,1),$M2>DATE(2015,4,30)),INDIRECT("list_a"),INDIRECT("list_b"))，确认能正确返回对应的下拉选项列表。
• 动态区域优化：如果Q列的目标区域是动态的（比如只包含有数据的单元格），可以用Set kolQ = Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row)来自动获取范围。

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