vba to search cell values in another workbook's column

Hi All,
I have a column "F" in workbook1 containing some values (obtained after using some excel formulas to extract and concatenate from other columns) like
blah-rd1
blah-rd5
blah-rd6
blah-rd48do I want to do this
blah-rd100
etc

I have another column "D" in workbook2 containing values like
rndm-blah-rd1_sgjgs
hjdf-blah-rd5_cnnv
sdfhjdf-blah-rd100_cfdnnv
ect

Basically "Blah-rdxx" is always present alongwith other strings in D column of workbook2

Now, what I want to do is -
If value in D column of workbook2 contains value of F column of workbook1 Then
copy corresponding value of S column of workbook2 in H column of workbook1 (5th column)


This is where I have reached so far but it doesnt copy anything probably coz there is some problem in Cells(w.Row, 2).Copy (w1.Cells(K, 5)) -

Please Login or Register  to view this content.

Thanks for your help in advance.

You're correct, Cells(w.Row, 2).Copy doesn't have a qualified worksheet. So it defaults to the currently active sheet which I'm guessing is Book1-Sheet1.

Try this...
w2.Cells(w.Row, 2).Copy w1.Cells(K, 5)

A couple of other comments:

1.) blah-rd1 will partial match with blah-rd100. A way around that is if the values in Book2 always have a trailing underscore e.g.; blah-rd100_, then if you search for blah-rd1_, it can't match blah-rd100_
v = r.Value & "_"

2.) If you only have one match, then after you copy\paste, use Exit For to stop searching for that rd value.

3.) The .Find method is a faster search.