excel - Application-defined or object-defined error for Type when adding data validation with VBA

Question

I am trying to use VBA to add data validation to a range in my document. The validation is supposed to compare the entry to a cell in another tab using a vlookup based on a unique id and prevent any percentage that is higher than the lookup value from being entered. I am getting a

Run-time error '1001' Application-defined or Object-defined error

and the debug is highlighting the .Add row.

I believe that it is the Type parameter that is hanging it up, but no matter what i change it to, it still gives me the error. I have been able to create the validation manually, so i know that it can work. I also have ensured that there are no existing validations and that the correct range is being selected. I'm lost as to what's going on.

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

question from:https://stackoverflow.com/questions/65867609/application-defined-or-object-defined-error-for-type-when-adding-data-validation

Answer 1

Your first vlookup argument should be a single cell: it will auto-adjust for the rest of the range.

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