VBA遍历工作表设置数据验证报错1004：跨表引用异常

这个问题我太熟悉了——VBA里的Cells默认绑定活动工作表，这是新手常踩的坑！你的1004错误完全是因为未指定Cells所属的工作表导致的跨表引用冲突。

错误根源分析 #

当你在循环里写Worksheets(Works.Name).Range(Cells(1, ColumnList), Cells(46, ColumnList))时，Cells并没有指定属于Works这个工作表，它默认指向当前活动工作表（也就是你运行代码时选中的第一个表）。当循环到后续工作表时，Cells依然关联着第一个表，这就导致你试图让SheetX的Range去引用Sheet1的Cells，触发跨表引用的1004错误。

修正后的代码 #

Sub loopValidateTest()
    Dim Works As Worksheet
    Dim ColumnList As Integer ' 显式声明变量类型，避免隐式转换问题
    Dim ValidationRange As Range
    
    ' 用ThisWorkbook明确指定当前工作簿，防止误操作其他打开的工作簿
    For Each Works In ThisWorkbook.Sheets
        If Works.Name <> "Bilan_H" Then
            For ColumnList = 2 To 16
                ' 所有Cells都明确绑定当前遍历的Works工作表
                Set ValidationRange = Works.Range(Works.Cells(1, ColumnList), Works.Cells(46, ColumnList))
                
                ' 这里的Cells同样要指定为Works的
                If IsEmpty(Works.Cells(1, ColumnList)) = False Then
                    With Works.Range(Works.Cells(48, ColumnList), Works.Cells(2000, ColumnList)).Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                            Operator:=xlBetween, Formula1:="=" & ValidationRange.Address
                    End With
                End If
            Next ColumnList
        End If
    Next Works
End Sub

额外优化建议 #

• 在模块的最顶部添加Option Explicit，强制所有变量必须声明，能帮你快速发现拼写错误或未定义的变量。
• 如果不需要绝对引用，可将ValidationRange.Address改为ValidationRange.Address(False, False)，生成相对引用的地址，更灵活。
• 避免重复写Worksheets(Works.Name)，因为Works本身就是工作表对象，直接用Works更简洁高效。

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