title: Create multiple validation lists with variables
body: <p>I have 50 dynamic named ranges that are called "Line1_S", "Line2_S", "Line3_S",... to "Line50_S".</p>
  
  <p>On my sheet "Pricing Tool", I want to create 50 validation (drop down) lists in cells C7 to C56.</p>
  
  <p>Cell C7 should have named range "Line1_S", Cell C8 should have "Line2_S" and so forth until C56 has "Line50_S".</p>
  
  <p>I keep getting an error in the formula part of my code; can you please have a look and help me resolve the issue?</p>
  
  <pre><code>Sub test()
  
  Dim j As Integer
  
  For j = 1 To 50
  
      With Sheets("Pricing Tool").Cells(j + 6, 3).Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
          xlBetween, Formula1:="=Line" &amp; j &amp; "_S" ' this is where the error occurs
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
      End With
  
  Next
  
  End Sub
  </code></pre>
  
  <p>Many thanks for your help!</p>
  
  <p>Edit - below a sample named range:</p>
  
  <pre><code>=OFFSET('Size Selection'!$E$4, 0, 0, 1, COUNT(IF('Size Selection'!$E$4:$AJ$4="", "", 1)))
  </code></pre>