问题描述
我正在尝试创建一个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.
这篇关于更新数据验证列表范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!