troubleshooting Question

Data Validation List directly from UDF Function

Avatar of dlmille
dlmilleFlag for United States of America asked on
Microsoft Excel
6 Comments2 Solutions629 ViewsLast Modified:
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
DV-ListFun-r4.xls
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros