Results 1 to 1 of 1

Macro to return multiple values from search value

Threaded View

Mr Low Macro to return multiple... 07-11-2012, 02:00 PM
  1. #1
    Registered User
    Join Date
    07-11-2012
    Location
    Cape Town
    MS-Off Ver
    Excel 2010
    Posts
    1

    Macro to return multiple values from search value

    Good day/evening all

    I am by no means a VBA expert but I managed to find a useful function designed to give multiple values for my search value from a specified array. The function does the job but how could I edit the syntax to be able to seperate my lookup values which is in a concatenated form.

    eg.

    ColumnA ColumnB
    Product_id Promotion_id
    112233 10001111, 10001322, 10003222
    ect.

    This is as I referenced the syntax:

    Function vlookupall(sSearch As String, rRange As Range, _
        Optional lLookupCol As Long = 2, Optional delimit As String = ",") As String
    'Vlookupall searches in first column of rRange for sSearch and returns
    'corresponding values of column lLookupCol if sSearch was found. All these
    'lookup values are being concatenated, delimited by sDel and returned in
    'one string. If lLookupCol is negative then rRange must not have more than
    'one column.
    'Reverse("moc.LiborPlus.www") PB 16-Sep-2010 V0.20
    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 = delimit
        End If
    Next i
    Any ideas on how I can tweak this to separate my referenced values (10001111, 10001322, 10003222) to cells within different columns

    eg.

    ColumnA ColumnB ColumnC ColumnD
    Product_id Promo_id Promo_id Promo_id
    112233 10001111 10001322 10003222

    I hope this extended narrative helps.
    Kind regards
    Last edited by Mr Low; 07-11-2012 at 02:02 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