Macro using VLOOKUP with multiple matches between multiple sheets

**Derrek0204** · 08-15-2011, 05:40 PM

I have a macro that compares data in Sheet1 Column B with Sheet2 Column C. If they match, the value in Sheet1 Column E is copied to Sheet2 Column D. Right now the macro only lists the first match and ignores any other matches after that. How do I modify the macro to include all matches in one long string (separated by spaces)?

[code]
Sub PlaceVLookupTMS()
' place in a standard module
With Sheets("Sheet2")
.Range("D2:D" & .Range("A" & .Rows.Count).End(xlUp).Row).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],Sheet1!C[-2]:C[1],4,FALSE),"""")"
End With
End Sub
[code]

**JBeaucaire** · 08-15-2011, 07:31 PM

1) fix that [/code] tag at the end above...

2) In your sample sheet, how about some sample results?

**Derrek0204** · 08-16-2011, 08:30 AM

Sorry about that. Attached are the results of the macro. If you see in Sheet 2 D1, the IBC number only comes back with the first result which is 234823WUIA and fails to display 21232123 which also matches. Any clues on how to modify the macro? Thanks in advance for your help!

Sub PlaceVLookupTMS()
' place in a standard module
With Sheets("Sheet2")
.Range("D2:D" & .Range("A" & .Rows.Count).End(xlUp).Row).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],Sheet1!C[-2]:C[1],4,FALSE),"""")"
End With
End Sub