使用列表验证单元格:应用程序定义的错误或对象定义的错误

I am using the code below to add validation lists to various cells. I thought it simple enough, but I get an error at the Formula1:="Notes!A1" & finalRowNotes line. The error is

我正在使用下面的代码向不同的单元格添加验证列表。我认为它足够简单，但我在公式中得到了一个错误:="Notes! "A1“& finalRowNotes线。错误的是

Application defined or object defined 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 is what is in the Notes worksheet in cells A1:A18

对于它的价值，这里是在单元格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

4 个解决方案

#1

Based on the conversations in both the question and the first answer, I would recommend placing the following code in a module:

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

'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

#2

I don't know what to say, and I am sorry for wasting everyone's time, but when I restored this workbook to an earlier version, all was fine. All I can think to say is thanks to all who threw their hats in the ring and gave such generous efforts. I suppose my workbook became corrupted somehow because an older, yet identical one, worked as expected.

我不知道该说什么，很抱歉浪费了大家的时间，但是当我把这本练习册恢复到以前的版本时，一切都很好。我想说的是，感谢所有在拳击台上抛头露面并做出如此慷慨努力的人。我想，我的工作簿因某种原因而变得腐朽了，因为一个更老的，但又完全相同的，正如预期的那样工作。

#3

You use operator xlBetween. I guess, you need xlEqual.

你使用xlBetween运营商。我猜，你需要xlEqual。

#4

just activate the range, it will solve the problem..

只要激活范围，就能解决问题。

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

#1