更新数据验证列表范围 [英] Update Data Validation List Range

问题描述

我正在尝试创建一个vba代码，该代码会将我的数据验证列表范围更新为另一张纸上的最后一行. 我的下拉列表位于我的主页"选项卡上，该列表的列表范围来自映射选项卡"上的P列.该列表每天都会更改，因此希望添加公式以查找最终行，而不是输入单元格行值.这是我编写的代码，但公式出现错误.

I'm trying to create a vba code that will update my data validation list range to last row on another sheet. My dropdown list is on my "Home" tab which takes its list range from column P on "Mapping tab". The list will change daily so wanted to add in the formaula to look for finalrow rather than entering a cell row value. Here is the code I have written but a error occurs with the formula.

Sub getDropdownList()

    Dim finalrow1 As Integer

    'finds last row in Column P on Mapping tab
    Sheets("Mapping").Select
    finalrow1 = ActiveSheet.Cells(Rows.Count, "P").End(xlUp).Row

    Sheets("Home").Select
        Range("E7").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Mapping!$P$1:$P &finalrow1"

    End With

    End Sub

推荐答案

我不知道这是否可行，但尝试将公式更改为

I have no idea if this will work but try changing the formula to

Formula1:="=Mapping!$P$1:$P" & finalrow1

excel可能会将您的整数当作字符串对待，并且感到困惑.

excel is probably treating your integer as a string and getting confused.

这篇关于更新数据验证列表范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！