Excel VBA复制表格单元格数据验证触发1004错误的原因与解决方法

你的1004错误主要来自两个核心问题：

1. 公式语法错误（根源） #

你提到的报错Formula1 =COUNTIF(INDIRECT("tblCalibre[@CalibreName]"));INDIRECT("tblCountry[@Country]"))=1 存在明显语法问题：COUNTIF函数需要两个必填参数（统计区域 + 匹配条件），但你的公式提前闭合了第一个参数的括号，导致后续的分号和第二个参数变成无效语法。正确的公式应该是：

=COUNTIF(INDIRECT("tblCountry[Country]"); INDIRECT("tblCountry[@Country]"))=1

虽然你说第二行的验证是正常存在的，但可能Excel在单元格中对轻微语法问题有容错，但VBA添加验证时的语法检查更严格，直接触发了错误。

2. 手动复制验证属性的局限性 #

你逐个复制Validation的Type、AlertStyle、Formula1等属性时，容易忽略两个细节：

• 区域设置的参数分隔符差异：如果你的Excel是欧洲区域设置（用分号;作为函数参数分隔符），但VBA内部默认用逗号,作为分隔符，直接读取valMasterValidation.Formula1会导致分隔符不匹配，触发语法错误。
• 隐藏属性遗漏：Validation还有InputTitle、InputMessage等属性，手动复制容易遗漏，也可能间接引发错误。

方法1：使用Validation自带的Copy方法（最推荐） #

Excel的Validation对象自带Copy方法，可以直接把源验证的所有属性完整复制到目标单元格，完全避免手动赋值的各种问题：

Dim valMasterValidation As Validation
' 获取第二行的源验证对象（确保该行该列确实有有效验证）
Set valMasterValidation = recordInTable.ListRows(2).Range(columnInTable.Index).Validation

' 遍历目标列的所有数据单元格
Dim targetCell As Range
For Each targetCell In recordInTable.ListColumns(columnInTable.Index).DataBodyRange
    ' 跳过源验证所在的第二行
    If targetCell.Row <> valMasterValidation.Parent.Row Then
        ' 先清除目标单元格已有的无效验证（避免冲突）
        On Error Resume Next
        targetCell.Validation.Delete
        On Error GoTo 0
        
        ' 一键复制源验证到目标单元格
        valMasterValidation.Copy Destination:=targetCell.Validation
    End If
Next targetCell

方法2：修复手动赋值的公式问题 #

如果你坚持要手动赋值，需要先修正公式语法，再兼容不同区域的参数分隔符：

Dim valMasterValidation As Validation
Set valMasterValidation = recordInTable.ListRows(2).Range(columnInTable.Index).Validation

' 添加错误捕获，确保源验证存在
On Error Resume Next
If valMasterValidation Is Nothing Then
    MsgBox "第二行该列无有效数据验证，请检查！"
    Exit Sub
End If
On Error GoTo 0

Dim targetCell As Range
For Each targetCell In recordInTable.ListColumns(columnInTable.Index).DataBodyRange
    If targetCell.Row <> valMasterValidation.Parent.Row Then
        On Error Resume Next
        targetCell.Validation.Delete
        On Error GoTo 0
        
        With targetCell.Validation
            ' 获取当前Excel的参数分隔符（兼容所有区域设置）
            Dim listSep As String
            listSep = Application.International(xlListSeparator)
            
            ' 修正源公式的分隔符，适配当前区域
            Dim correctedFormula1 As String
            correctedFormula1 = Replace(valMasterValidation.Formula1, ";", listSep)
            correctedFormula1 = Replace(correctedFormula1, ",", listSep)
            
            ' 添加验证
            .Add Type:=valMasterValidation.Type, _
                 AlertStyle:=valMasterValidation.AlertStyle, _
                 Operator:=valMasterValidation.Operator, _
                 Formula1:=correctedFormula1, _
                 Formula2:=IIf(valMasterValidation.Formula2 = "", "", Replace(valMasterValidation.Formula2, ";", listSep))
            
            ' 复制所有其他属性
            .IgnoreBlank = valMasterValidation.IgnoreBlank
            .ErrorTitle = valMasterValidation.ErrorTitle
            .ErrorMessage = valMasterValidation.ErrorMessage
            .InputTitle = valMasterValidation.InputTitle
            .InputMessage = valMasterValidation.InputMessage
            .ShowInput = valMasterValidation.ShowInput
            .ShowError = valMasterValidation.ShowError
        End With
    End If
Next targetCell

额外注意事项 #

• 操作Validation前必须先删除目标单元格已有的验证，否则会因重复添加触发错误。
• 建议给源验证的获取步骤添加错误捕获，避免因第二行无验证导致的后续代码崩溃。

内容的提问来源于stack exchange，提问作者J. Vos