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
Bookmarks