title: VBA Data validation list not working (Formula1:=)
body: <p>The following code is a loop that takes the value of a cell (string), and inserts it into another sheet. it then separates the values of the string by a comma delimiter. It all works fine until I try to create a list data validation, where the <code>Formula1:=</code> does not seem to be working properly, however the code looks fine. </p>
  
  <p>It is supposed to copy the current row of the loop all the way to the last column and create a data validation list out of it. </p>
  
  <p>Please help, what am I doing wrong?</p>
  
  <pre><code>Sub dataVal()
  
      Dim lrow As Long
      Dim lcol As Long
      Dim i As Long
      Dim counter As Integer
      counter = 1
      lrow = Sheets("LVL &amp; Mapping").Cells(Sheets("LVL &amp; Mapping").Rows.count, "H").End(xlUp).Row
      lcol = Sheets("Sheet7").Cells(counter, Columns.count).End(xlToLeft).Column
  
      For i = 4 To lrow
        Range("I" &amp; i).Select
        Selection.Copy
        Sheets("Sheet7").Select
        Range("A" &amp; counter).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Selection.TextToColumns Destination:=Range("A" &amp; counter), DataType:=xlDelimited, _
          TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
          Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
          :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
          TrailingMinusNumbers:=True
        Sheets("LVL &amp; Mapping").Select
        Range("J" &amp; i).Select
          With Selection.Validation
             .Delete
             .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
          xlBetween, Formula1:=Sheets("Sheet7").Range(Cells(counter, 1), Cells(counter, lcol))
             .IgnoreBlank = True
             .InCellDropdown = True
             .InputTitle = ""
             .ErrorTitle = ""
             .InputMessage = ""
             .ErrorMessage = ""
             .ShowInput = True
             .ShowError = True
          End With
        counter = counter + 1
      Next i
  End Sub
  </code></pre>