使用VBA添加数据验证时，Type的应用程序定义或对象定义错误

Question

我试图使用VBA将数据验证添加到我的文档中的范围。验证应该使用基于唯一id的vlookup将条目与另一个选项卡中的单元格进行比较，并防止输入任何高于查找值的百分比。我得到一个

运行时错误'1001'应用程序定义或对象定义错误

和调试突出显示.Add行。

我相信是Type参数挂起了它，但不管我把它改成什么，它仍然给我错误。我已经能够手动创建验证，所以我知道它可以工作。我还确保没有现有的验证，并且选择了正确的范围。我不知道发生了什么事。

Workbooks("Comp_Worksheet.xlsx").Worksheets("Sheet1").Activate
Dim MyRange As Range
Dim LastRow As Long
Dim WB As Workbook
Set WB = Workbooks("Comp_Worksheet.xlsx")
LastRow = WB.Sheets(1).Range("D" & Rows.Count).End(xlUp).Offset(0, -1).Row
Set MyRange = Workbooks("Comp_Worksheet.xlsx").Worksheets("Sheet1").Range("AD5:AD" & LastRow)
MyRange.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator:=xlLessEqual, Formula1:="=VLOOKUP(D5:D" & LastRow & ",Sheet2!A:D,4,FALSE)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Rec Bonus % Exceeds Max Range"
.ErrorMessage = "Rec Bonus % must be equal to or less than the Bonus Performance Range maximum. Press CANCEL and enter a new percentage."
.ShowInput = True
.ShowError = True
End With

Answer 1

您的第一个vlookup参数应该是单个单元格:它将自动调整范围的其余部分。

Dim WB As Workbook, ws As Worksheet
Set WB = Workbooks("Comp_Worksheet.xlsx")
Set ws = WB.Worksheets("Sheet1") 'you also use .Sheets(1), which might not be the same sheet...
With ws.Range("F5:F" & ws.Cells(ws.Rows.Count, "D").End(xlUp).Row).Validation
.Delete
'set the first vlookup argument to the first cell in the range: it will auto-adjust
'  for the rest of the range
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, _
Operator:=xlLessEqual, Formula1:="=VLOOKUP(D5,'Sheet2'!A:D,4,FALSE)"
.InputMessage = "% must be <= Range Max."
.ErrorTitle = "Rec Bonus % Exceeds Max Range"
.ErrorMessage = "Rec Bonus % must be equal to or less than the Bonus Performance Range maximum. Press CANCEL and enter a new percentage."
.ShowInput = True
.ShowError = True
.IgnoreBlank = True
.InCellDropdown = True
End With