How to search a string in a cell and paste founded value in next cell ?

[Ken]

Hi Experts,

Need your help/feedback in how to find a value in a cell. After finding store it in next column.

[pre]
1) Source file (array)
ItemId
I1001
I1005
I1004
I1993
I3940

2) Target file (Sheet1)
Column A Column B Column C
Date CustomerId Itemid
1/1/2017 C0001 I1005,I1004
1/2/2017 C0007 I3950,I1994
1/3/2017 C0079 I1004,I1993
[/pre]
3) E.g. From Source select an id I1004 and search in Target file,
On finding value in a cell, store the found value in next column of the row.
if not found do until all source file is scanned

sub proc_Lookup
    dim strSource, strCheck, strResult, strTarget as string
    dim i, iLen, iMidStart, iInStr as integer
			
    'SourceList
    dim Arry(5) as string
    Arry(1) ="I1004"
    Arry(2) ="I1005"
    Arry(3) ="I1993"
    Arry(4) ="I3940"
    Arry(5) ="I1001"	 
			
    Workbooks("Target.xls").activate
    Worksheets("Sheet1").activate	 
[COLOR=#EF2929]
[b]Step - need help
    1) To search Arry(1) value  in Sheet1 of each row
    2) if found add value in column D of the row found till the end of sheet1 that has value
    3) if not found select next arry and do step 2[/b]
[/color]
end sub

Thanks,
Ken

 

[Andrzejek]

I my simple mind, if you want to check if the elements of your Arry are in Column C, and display it in column D:

Option Explicit

Sub proc_Lookup()
Dim i As Integer
Dim r As Integer
    'SourceList
Dim Arry(5) As String
Arry(1) = "I1004"
Arry(2) = "I1005"
Arry(3) = "I1993"
Arry(4) = "I3940"
Arry(5) = "I1001"

For i = LBound(Arry) To UBound(Arry)
    If Len(Arry(i)) > 0 Then
        r = 2
        Do While Cells(r, 3).Value <> ""
            If InStr(Cells(r, 3).Value, Arry(i)) Then
                If Len(Cells(r, 4)) = 0 Then
                    Cells(r, 4) = Arry(i)
                Else
                    Cells(r, 4) = Cells(r, 4) & "," & Arry(i)
                End If
            End If
            r = r + 1
        Loop
    End If
Next I

End Sub

You get:

[pre]
Column C Column D
I1005,I1004 I1004,I1005
I3950,I1994
I1004,I1993 I1004,I1993
[/pre]
By the way,
When you declare your variables this way:

dim [red]strSource, strCheck, strResult[/red], [blue]strTarget[/blue] as string
dim [red]i, iLen, iMidStart[/red], [blue]iInStr[/blue] as integer

Only [blue]Blue[/blue] variables are of the type you have declared, all [red]red[/red] variables are Variants :-(

---- Andy

There is a great need for a sarcasm font.

 

[Ken]

Hi Andrzejek,

Thank you, it works.

Ken