title: Adding validation list to dynamic cell range via VBA in Excel 2013
body: <p>I'm just beginning to get into Excel VBA coding, having basic VB knowledge and searching for the appropriate solutions on the net ;)
  I'm working on  a staff rostering solution in Excel. It is growing quite complex for a newbie, but I've got time and I am eager to learn. </p>
  
  <p>On my main worksheet, the number of columns equals the number of days for the current month and the number of rows is created by a vba function that loads a list of all clients from another worksheet, so far, so good.</p>
  
  <p>Now i want to populate the validation lists of each cell in this range (firstDayfirstClient:lastDaylastClient) with the list of employee IDs from yet another worksheet. To do so, I managed to write the following function:</p>
  
  <pre><code>Sub createDD()
  Dim bSh As Worksheet
  Set bSh = Sheets("sht_MA")
  Sheets("sht_Main").Activate
  Dim c As Range
  c = Range(Cells(4, 4), Cells(Cells(Rows.Count, 1).End(xlUp).Row, _
      Cells(3, 256).End(xlToLeft).Column))
  With c.Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
       Operator:=xlBetween, Formula1:=bSh.Range("tbl_MA[ID]")
      .IgnoreBlank = True
      .InCellDropdown = True
  End With
  End Sub
  </code></pre>
  
  <p>This  always throws me an error 91 (with block variable not set), even if I hardcode the Range with  </p>
  
  <pre><code>c = Range("D4", "AH6")
  </code></pre>
  
  <p>Can anyone tell me if I am missing something or haven't got the concept right? I have also tried to not use the active worksheet when defining the range but to address it explicitly like so</p>
  
  <pre><code>Dim aSh, bSh As Worksheet
  Set bSh = Sheets("sht_MA")
  Set aSh = Sheets("sht_Main")
  Dim c As Range
  c = aSh.Range(aSh.Cells(4, 4), aSh.Cells(aSh.Cells(aSh.Rows.Count, 1) _ 
      .End(xlUp).Row, aSh.Cells(3, 256).End(xlToLeft).Column))
  </code></pre>
  
  <p>which is more confusing but doesn't help either.</p>
  
  <p>I hope this gets across what I am trying to say, as I am neither a native speaker of English nor VBA ;)
  Any help is greatly appreciated!</p>
  
  <p>Chris</p>