title: Setting validation via VBA in excel fails when using variable
body: <p>I am trying to set the data validation for a range of cells using VBA. I get a run-time error 1004 (so helpful) "Application defined or object defined error" with this code.</p>
  
  <pre><code> With rngRangeToCheck.Cells(lrownum, 1).Validation
      .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
          xlBetween, Formula1:=choice
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
  End With
  </code></pre>
  
  <p>In Formula1, choice is a variable passed to the function that resembles "=SomeNamedRange" from the workbook the code is in.</p>
  
  <p>The error occurs on the <code>.Add</code> section of the code.</p>
  
  <p>If I hard-code Formula1 as <code>Formula1:="=SomeNamedRange"</code> it works without a problem.I'd really rather not hard-code it, because I am doing this with a lot of possible values for 'choice' and that would just be less-than-clean code, I think.</p>
  
  <p>I have been burning up Google and about 3 different books for days now trying to sort this out. </p>
  
  <p>Any suggestions? Thanks for helping a newbie out.</p>