VBA为可变行数表格单元格创建数据验证列表出错排查

问题根源 #

你的代码主要有3个问题导致报错或显示异常：

• 未指定工作表的Range引用：LastRow计算和循环的Range("C5:D" & LastRow)都没绑定到wsData（Rouge工作表），默认会用当前活动表，导致范围错误。
• 带空格的工作表名未加单引号：Support Gant表名含空格，直接拼接成公式时Excel无法识别，这是Formula1处报错的核心原因。
• 冗余循环与低效操作：没必要逐个单元格添加验证，直接给目标区域批量设置更高效。

修正后的代码 #

Sub AddTimeValidation()
    Dim wsData As Worksheet
    Dim wsSupportGant As Worksheet
    Dim rngTarget As Range
    Dim strFormula As String
    Dim lastRow As Long
    
    ' 绑定工作表对象
    Set wsData = ThisWorkbook.Sheets("Rouge")
    Set wsSupportGant = ThisWorkbook.Sheets("Support Gant")
    
    ' 计算Rouge表中C列最后一行（确保取到正确的可变表格行数）
    lastRow = wsData.Cells(wsData.Rows.Count, "C").End(xlUp).Row
    
    ' 定义要添加验证的目标区域：C2到D列最后一行（原代码起始行为C5可自行修改）
    Set rngTarget = wsData.Range("C2:D" & lastRow)
    
    ' 构建数据验证公式：带空格的表名必须加单引号包裹
    strFormula = "='" & wsSupportGant.Name & "'!" & wsSupportGant.Range("A2:A23").Address
    
    ' 批量设置数据验证
    With rngTarget.Validation
        .Delete ' 清除原有验证
        .Add Type:=xlValidateList, Formula1:=strFormula
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End Sub

关键说明 #

• 表名含空格时，公式里必须用'表名'!格式，否则Excel会解析为语法错误。
• 所有Range操作都明确绑定到对应工作表，避免活动表切换导致的错误。
• 直接对整个目标区域设置验证，比循环每个单元格效率更高，尤其当表格行数较多时。

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