Solved: Data Validation List directly from UDF Function

Per related question, Please see attached workbook.

Please notice in the spreadsheet, I use this function with an INDEX to get the 1 to many results for a criteria-based list, which is then identified by a data validation cell, and all works correct. See attached excel file, the cell to focus on is the yellow one which references column B whose formulas are referencing this function.

Of course, having this "parallel" list in column B is a little awkward, and without doing something fancy with a combobox or something, I'm wondering whether a DV List will accept a comma separated list of values direclty, via a UDF function.

Your assistance would be appreciated in modifying the attached code such that the results can go directly to the DV list. It would be great not to have to have Column B to make this all work.

Dave

Function subStrList(kwd As Range, DVList As Range) As Variant
Dim foundRange As Range, lastRow As Long
Dim myDVList() As String, cnt As Long

    lastRow = DVList.Cells(1, 1).Row + DVList.Rows.Count - 1

    Set foundRange = DVList.Find(what:=kwd, LookIn:=xlValues, lookat:=xlPart)
    If Not foundRange Is Nothing Then
        'subStrList = foundRange.Value
        ReDim Preserve myDVList(cnt) As String
        myDVList(cnt) = foundRange.Value
        cnt = cnt + 1
        Do
            Set foundRange = DVList.Find(what:=kwd, after:=foundRange, LookIn:=xlValues, lookat:=xlPart)
            If Not foundRange Is Nothing Then
                'subStrList = subStrList & "," & foundRange.Value
                ReDim Preserve myDVList(cnt) As String
                myDVList(cnt) = foundRange.Value
                cnt = cnt + 1
            End If
        Loop While Not foundRange Is Nothing And foundRange.Row < lastRow
    End If
    
    subStrList = myDVList
End Function

Open in new window

DV-ListFun-r4.xls