Macro using VLOOKUP between multiple sheets

[Derrek0204]

I need a macro using VLOOKUP to compare data in Sheet1 Column B with Sheet2 Column C. If they match, I want to have the value in Sheet1 Column E to be copied to Sheet2 Column D.

I found this macro online and have modified it but I dont think I have the VLOOKUP formula correct. I have that part in red. Please help!

[code]
Sub PlaceVlookup()
'Determine last row based on Sheet 1 Column B
lastRow = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
'Insert formula using lastRow variable
Sheets(2).Range("D1:D" & lastRow).FormulaR1C1 = _
"=VLOOKUP(RC[1],Sheet2!RC[3]:R[" & lastRw - 1 & "]C[2],3,0)"'Copy/PasteSpecial Values
With Sheets(2).Range("D1:D" & lastRow)
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End Sub
[code]

[TMS]

Please post a sample workbook

Regards

[Derrek0204]

Attached is a sample workbook.

[TMS]

Try:

Please Login or Register  to view this content.

Regards

[Derrek0204]

Thanks! It works perfectly!

[TMS]

You're welcome. Thanks for the rep.

If this has answered your question, please mark your thread as solved. See my signature for details or the FAQ.

Regards

[mkabs11]

I am kinda of new VBA, where exactly did you add that Sub?

[mkabs11]

O, I see, TMShucks just created a new sub and that works perfectly!
Thank you

[Derrek0204]

I am running into a problem with this. What if there are multiple book numbers that are the same but have different IBC numbers? How would I include all applicable IBC numbers without deleting any? It could be one long string.

[TMS]

Your original question was asked and answered.

This is a variation/extension to the original problem and I suggest that you open a new thread and upload a sample workbook with some data that demonstrates the issue.

Regards

[indigobluez]

I am running the macro on the downloaded sheet, the IBC column is all getting replaced by #REF!