I currently have a UDF that conducts a vlookup search and returns all values that meet the search criteria. The vlookup portion of the code works very effectively however the results are displayed in a concatenated fashion in one single cell. I would like to be able to change the UDF to display each returned value in its own cell. I know what portion of the code is creating the concatenation in the returned data string but I'm not sure how to change this to report in different cells. The UDF is displayed below:
Function VlookupAll(sSearch As String, rRange As Range, _
Optional lLookupCol As Long = 2, Optional sDel As String = ",") As String
Dim i As Long, sTemp As String
If lLookupCol > rRange.Columns.Count Or sSearch = "" Or _
(lLookupCol < 0 And rRange.Columns.Count > 1) Then
VlookupAll = CVErr(xlErrValue)
Exit Function
End If
VlookupAll = ""
For i = 1 To rRange.Rows.Count
If rRange(i, 1).Text = sSearch Then
If lLookupCol >= 0 Then
VlookupAll = VlookupAll & sTemp & rRange(i, lLookupCol).Text
Else
VlookupAll = VlookupAll & sTemp & rRange(i).Offset(0, lLookupCol).Text
End If
sTemp = sDel
End If
Next i
End Function
Bookmarks