Results 1 to 2 of 2

Help Editing UDF

Threaded View

  1. #1
    Registered User
    Join Date
    06-13-2011
    Location
    Boulder, Colorado
    MS-Off Ver
    Excel 2010
    Posts
    61

    Help Editing UDF

    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
    Last edited by Impartial Derivative; 07-11-2011 at 02:48 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1