vba - 使用列表验证单元格 : Application defined or object defined error

Question

我使用下面的代码将验证列表添加到各个单元格。我认为这很简单，但我在 Formula1:="Notes!A1"& FinalRowNotes 行收到错误。错误是

Application defined or object defined error

我错过了什么？

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

就其值(value)而言，以下是 Notes 工作表中单元格 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

根据问题和第一个答案中的对话，我建议将以下代码放入模块中:

'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