如何为Excel多行列设置基于另一表参数最值的数据验证规则

适用场景：配方表列对应不同配方、行对应参数，需要为除首列参数名外的所有数值单元格添加基于独立上下限的验证规则，自动适配后续列数调整。
原参数表结构如下：

完整VBA代码 #

Sub 批量添加参数验证()
    Dim ws As Worksheet, limitWs As Worksheet
    Dim lastRow As Long, lastCol As Long, i As Long
    
    ' 定义工作表对象，可修改为你实际的表名
    Set ws = ThisWorkbook.Sheets("配方表")
    Set limitWs = ThisWorkbook.Sheets("param_limits")
    
    ' 自动识别配方表有效范围：按A列判断最后一个参数行，按表头行判断最后一个有效列
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' 遍历所有参数行，假设第1行是表头从第2行开始，可按需调整起始行号
    For i = 2 To lastRow
        ' 选中当前行跳过首列后的所有有效单元格添加验证
        With ws.Range(ws.Cells(i, 2), ws.Cells(i, lastCol)).Validation
            .Delete
            .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=" & limitWs.Name & "!B" & i, Formula2:="=" & limitWs.Name & "!C" & i
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = "取值范围"
            .ErrorTitle = "输入不合法"
            ' 动态显示当前参数的上下限，也可替换为自定义固定文本
            .InputMessage = limitWs.Range("B" & i).Value & " 到 " & limitWs.Range("C" & i).Value
            .ErrorMessage = "输入值超出参数允许范围，请检查"
            .ShowInput = True
            .ShowError = True
        End With
    Next i
End Sub

使用说明 #

• 如果param_limits表中上下限不是存在B、C列，或者参数行和配方表存在固定偏移（比如配方表第2行对应limit表第5行），调整公式中的列号、行号计算逻辑即可
• 后续新增配方列后，重新运行一次代码即可自动识别新的列范围完成验证规则添加
• 提示文本可按需自定义，比如把InputTitle改回你原来的zakres即可

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