title: How to assign variable to Data Validation Formula1:= in Excel VBA?
body: <p>I am trying to assign a variable <code>Rng</code> to <code>Formula1:=.</code></p>
  
  <pre><code>Function DisplayName()
  
  Dim iLastRow As Integer ' This variable will get Last Cell which is not empty in a Column
  Dim Rng As Variant ' This variable is created for Dynamic Range selection in a sheet2 named as "BM"
  
  Dim sheetRef As Worksheet
  Set sheetRef = Sheets("BM")
  
  iLastRow = sheetRef.Cells(Rows.Count, "A").End(xlUp).Row
  Rng = sheetRef.Range("A2:A" &amp; iLastRow)
  
      With ActiveCell.Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=Rng
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = "Select From List"
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
      End With
  
  End Function
  </code></pre>
  
  <p>The following error message points to <code>.Add Type:=</code></p>
  
  <blockquote>
    <p>Run-time error '1004': Application-defined or object-defined error</p>
  </blockquote>
  
  <p>If I remove the variable and place <code>"=Sheet2!A2:A99"</code> it works.</p>