Hi All,

I have 7 columns in sheet1 & in sheet2 i have 2 columns with some
data.

In sheet2 Col A
I have some codes like
001
125
4563

Same codes repeated in sheet 1 in any of the columns ( Col B to Col
G )

I need to match sheet2 col A data with sheet1 Col B to col G, if it
matches i need data available in sheet1 col A in sheet 2 col B

I am using this code.

For i = 2 to 50

For a = 2 To 100
Frst = UCase(sheets("Sheet2").Cells(i,
"A").Value)

with sheets("Sheet1")
If Frst = UCase(.Cells(a, "B").Value) Or _
Frst = UCase(.Cells(a, "C").Value) Or _
Frst = UCase(.Cells(a, "D").Value) Or _
Frst = UCase(.Cells(a, "E").Value) Or _
Frst = UCase(.Cells(a, "F").Value) Or _
Frst = UCase(.Cells(a, "G").Value) then

Temp = .Cells(a, "A")
res = res & "," & Temp

End If
end with
Next a
If res < "" Then
sheets("Sheet2").Cells(i, "B") = WorksheetFunction.Substitute
(res, ",", "", 1)
res = ""
next i


Above vba works fine, but I have some exceptions.

Some code in sheet1 is like W001 or F001 or L001 or T001
but in sheet2 has 001, so exact will not find this cases.

How can i modify my code, Even i thought about wild seraches
like "*" & Frst & "*" = "*" & UCase(sheets("Sheet1").Cells(a,
"D").Value) & "*"

But pulls out the results where the code say 123001 or ABCED001
etc....

Please help me !!