Checking a cell using a list: application definition or object error

Question

I use the following code to add checklists to different cells. I thought it was simple enough, but I get an error in the line Formula1:="Notes!A1" & finalRowNotes. Error

A specific claimed or object error

What am I missing?

finalRowNotes = Worksheets("Notes").Cells(1000000, 1).End(xlUp).Row
For i = 4 To r - 1
    With Range("P" & i).Validation
      .Delete
      .Add Type:=xlValidateList, _
      AlertStyle:=xlValidAlertStop, _
      Operator:=xlBetween, _
      Formula1:="=Notes!A1:A" & finalRowNotes
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
    End With
  Next i

For what it's worth, here's what is in the Notes sheet in the cells A1:A18

Block volume not reported
Blocks
Blocks away
Blocks.  Foreign trades not incl. in volume or VWAP in this market
Could not inventory
Market closed
No foreign trades
Order canceled
Order complete
Order given after market
Order given mid-session
Out of limit
Out of limit, changed to market order after session closed
Out of limit-premium
Pending 1 day funding requirement
Pending custodian confirmation
Volume out of limit
Volume out of limit, limit reduced during session

Answer 1

, , , , , . , , , . , - , , , .

Answer 2

, :

'Dimension your two variables
Dim Notes As Worksheet, Vali As Worksheet

'Set the variables equal to the sheet names.
Set Notes = Sheets("Notes")
Set Vali = Sheets("NAME OF VALIDATION SHEET")

'Changes the Sheet reference to the variable Notes and changed your syntax to Rows.Count
finalRowNotes = Notes.Cells(Rows.Count, 1).End(xlUp).Row 'Rows.Count is better method for last row of document
For i = 4 To r - 1
    'Added Vali to define the sheet
    With Vali.Range("P" & i).Validation
      .Delete
      .Add Type:=xlValidateList, _
      AlertStyle:=xlValidAlertStop, _
      Operator:=xlBetween, _
      Formula1:="=Notes!A1:A" & finalRowNotes
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
    End With
  Next i

Answer 3

You are using the xlBetween operator. I think you need xlEqual.

Answer 4

just activate the range, it will solve the problem.

Vali.Range("P" & i).Activate