VBA数据验证中INDIRECT函数相对引用问题求助

问题背景 #

开发VBA宏实现：当Input!A1单元格值变化时，自动更新Lists!工作表指定单元格的数据验证下拉列表。当前代码可完成提示文本填充，但存在两个问题：

• 数据验证下拉仅显示对应A列单元格的文本内容（如A列是Scientific_Name，下拉只显示该字符串，而非同名表格的Deus/ex/machina选项）
• 尝试使用INDIRECT(A1)时，所有验证单元格的下拉内容完全一致，推测为相对引用失效导致

原因分析 #

1. 原代码中Formula1:="=INDIRECT(""A"" & ROW())"的ROW()是基于VBA执行上下文的行号，而非验证单元格自身的行号，导致相对引用逻辑错误
2. 直接使用INDIRECT(A1)时未明确相对引用规则，Excel会将其解析为绝对引用，所有单元格指向同一个A1单元格

修正后的代码 #

Sub UpdateDataValidation()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim inputVal As String
    Dim i As Integer
    Dim prompts As Variant
    Dim validationCells As Variant
    Dim targetCell As Range

    Set ws1 = ThisWorkbook.Sheets("Input")
    Set ws2 = ThisWorkbook.Sheets("Lists")
    inputVal = ws1.Range("A1").Value

    ' 清除原有内容和数据验证
    ws2.Cells.ClearContents
    ws2.Cells.Validation.Delete

    ' 根据Input值定义提示文本和需设置验证的单元格
    Select Case inputVal
        Case "Brown"
            prompts = Array("Name", "Scientific_Name", "Gender", "Species", "Count", _
                            "Favorite_Food", "Favorite_Color", "Favorite_Sport", "Favorite_Drink", "Date", "Option_1", "Option_2")
            validationCells = Array("B1", "B2", "B5", "B7")
        Case "Orange"
            prompts = Array("Color", "Fabric", "Style", "Quantity", "Length", "Width", "Area", _
                            "Supplier", "Location", "Distance")
            validationCells = Array("B2", "B4", "B6", "B8", "B12")
        Case Else
            prompts = Array()
            validationCells = Array()
    End Select

    ' 填充提示文本到A列
    For i = LBound(prompts) To UBound(prompts)
        ws2.Cells(i + 1, 1).Value = prompts(i)
    Next i

    ' 为指定单元格设置动态数据验证
    For i = LBound(validationCells) To UBound(validationCells)
        Set targetCell = ws2.Range(validationCells(i))
        With targetCell.Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                 Formula1:="=INDIRECT(RC[-1])"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
    Next i
End Sub

关键改动说明 #

• 使用R1C1相对引用样式：RC[-1]表示当前验证单元格左侧（A列）的单元格，确保每个验证单元格都指向自己对应的A列文本，通过INDIRECT调用同名的Excel表格区域
• 新增targetCell变量明确指向当前待设置验证的单元格，提升代码可读性
• 保留原有动态切换逻辑，确保Input!A1值变化时，提示文本和验证范围同步更新

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