title: Setting cell data validation as list from dynamic range
body: <p>I'm trying to set cell data validation from a list which is stored in my worksheet using VBA macro. I don't know how long the list will be so the range needs to be dynamically selected. </p>
  
  <p>At the moment the line .Add Type:= Formula1:="=perfGradeRange" is throwing runtime error '1004' application defined or object defined error.</p>
  
  <p>My code is this:</p>
  
  <pre><code>Sub Perf_Grade_Dropdown()
  
  Dim perfGradeData As Worksheet
  Dim usedRange As range
  Dim rLastCell As range
  Dim range As range
  Dim perfGradeRange As range
  
  Set perfGradeData = Worksheets("Values")
  
  perfGradeData.Unprotect Password:="pass"
  
  perfGradeData.Activate
  
  Set rLastCell = perfGradeData.Cells.Find(What:="*", After:=Cells(1, 1),    LookIn:=xlFormulas, LookAt:= _
  xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
  
  Set perfGradeRange = perfGradeData.range(Cells(1, 1), rLastCell)
  
  Set range = perfGradeData.range(Cells(3, 3), Cells(4, 3))
  
      With range.Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="=perfGradeRange"
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
  End With
  
  perfGradeData.Protect Password:="pass", DrawingObjects:=True, contents:=True,          Scenarios:=True, userinterfaceonly:=True, _
  AllowSorting:=True, AllowFiltering:=True, AllowDeletingColumns:=True,    AllowInsertingColumns:=True
  perfGradeData.EnableAutoFilter = True
  
   End Sub
  </code></pre>
  
  <p>I've seen the following two questions already asked but haven't been able to get any of the suggestions to work:</p>
  
  <p><a href="https://stackoverflow.com/questions/3299478/setting-validation-via-vba-in-excel-fails-when-using-variable">Setting validation via VBA in excel fails when using variable</a></p>
  
  <p><a href="https://stackoverflow.com/questions/445519/how-do-i-avoid-run-time-error-when-a-worksheet-is-protected-in-ms-excel">How do I avoid run-time error when a worksheet is protected in MS-Excel?</a></p>
  
  <p>Appreciate any help.</p>