I'm building a comma delimited string through VBA to add a data validation list to a cell.
Split out your CSV values into a column on your worksheet. Then use a lookup formula or '=COUNTIF(A:A,"fa")' to check for a matching value in the column. Use Split Function to extract CSV to an Array.
Dim strCSV As String
strCSV = "doo,rae,me,fa,so,lay,de,doe"
arySubList = fSplitStr(strCSV)
For i = 0 To UBound(arySubList)
nRow = i + 2
Cells(nRow, 1) = arySubList(i)
Private Function fSplitStr(strVal As String)
If InStr(strVal, ",") > 0 Then
fSplitStr = Split(strVal, ",", , vbTextCompare)
Just in case others may look at this post here's another way to do it.
My preferred route to have done this would have been by declaring a range value as the list's source, but as this range was on a different sheet I wasn't able to do it.
I subsequently found out that if you name the range on the sheet, then you can reference this directly in the VBA.
The code now looks a little like this (Please bear in mind I need to create multiple lists and their dynamic in so much as based on which sheet is selected. MailPack is the range and 4 relates to column 'D' as this is where I want it.)
Sub cdAddDropDown(strWhat As String, intRow As Integer, intColumn As Integer)
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & strWhat