title: Data Validation List in excel VBA
body: <p>I am trying to add values to the cell in a list format if certain criteria is met. I initially was unable to assign the values directly so i created an Array to old the values that meet the criteria. however now when i try to assign the values of the array to a cell in a list format with the below code i get a </p>
  
  <blockquote>
    <p>type mismatch error</p>
  </blockquote>
  
  <pre><code>ThisWorkbook.Sheets("Plan").Cells(i, 5).Validation.Add _
        Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:=Join(St, ",")
  </code></pre>
  
  <p>Can anyone suggest how to achieve this or if there is a way to assign the values directly without holding them in an array. remember the list is not continuous an it only needs to select the values that meet my condition.</p>
  
  <pre><code>Sub try() 
  Dim j,k As Long 
  Dim c As Range 
  Dim st As Object 
  Set st=CreateObject("System.Collections.ArrayList") 
  For j=5 To 150 
  If(Workbook.Sheets("Plan").Cells(i,1)= Workbook.Sheets("Master").Cells(j,1))Then 
      For k= 6 To 150 
          If(Workbook.Sheets("Master").Cells(j,k)&lt;&gt;"") Then
              For Each c In Workbook.Sheets("Master").Cells(1,k)
                  Select Case c.Value 
                      Case"Starch" st.Add Workbook.Sheets("Master").Cells(3,k)
                  EndSelect 
              Nextc 
          EndIf 
      Next k 
  EndIf 
  Next j 
  
  Workbook.Sheets("Plan").Cells(i,5).Validation.Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, Formula1:=Join(st,",") 
  
  End Sub
  </code></pre>