×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
Hi Experts,

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

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


CODE

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	 

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

end sub 

Thanks,
Ken

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

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:

CODE

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:

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

CODE

dim strSource, strCheck, strResult, strTarget as string
dim i, iLen, iMidStart, iInStr as integer 

Only Blue variables are of the type you have declared, all red variables are Variants sad

---- Andy

There is a great need for a sarcasm font.

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

(OP)
Hi Andrzejek,

Thank you, it works.

Ken

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close