+ Reply to Thread
Results 1 to 2 of 2

Help Editing UDF

Hybrid 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.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,927

    Re: Help Editing UDF

    when I've had to do this, I've set it up to return an array. This means the UDF has to be entered as an array function over the range where you want the results, but it generally works pretty well. Adapted code:
    Function VlookupAll(sSearch As String, rRange As Range, _
        Optional lLookupCol As Long = 2, Optional sDel As String = ",") As Variant
    
    Dim i As Long, sTemp As String
    dim temparray(35) as string (adjust array size to needs, or use Redim to make array size variable)
    
    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
                temparray(i)= rRange(i, lLookupCol).Text
            Else
                temparray(i)= rRange(i).Offset(0, lLookupCol).Text
            End If
            sTemp = sDel
        End If
    Next i
    vlookupall=temparray
    End Function

+ Reply to Thread

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