VBA to use Vlookup on another spreadsheet.

[ManxBen]

Hi All

Looking for some help with a VBA code.

Each day I get 2 reports generated from different systems.

The first shows the current status of orders and the second shows whether money has been received.

The first spreadsheets data looks like:

[TABLE="width: 503"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Status[/TD]
[TD]Order Number[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]01/11/2016[/TD]
[TD]Pending payment[/TD]
[TD]111111111[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]15/11/2016[/TD]
[TD]Pending payment[/TD]
[TD]222222222[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]17/11/2016[/TD]
[TD]Pending payment[/TD]
[TD]333333333[/TD]
[/TR]
[TR]
[TD]Leah[/TD]
[TD]20/11/2016[/TD]
[TD]Pending payment[/TD]
[TD]444444444[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]21/11/2016[/TD]
[TD]Pending payment[/TD]
[TD]555555555[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]21/11/2016[/TD]
[TD]Pending payment[/TD]
[TD]666666666[/TD]
[/TR]
</tbody>[/TABLE]


The Second reports data looks like


[TABLE="width: 530"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Order Number[/TD]
[TD]Currency Received[/TD]
[TD]Amount Received[/TD]
[TD]Date received[/TD]
[/TR]
[TR]
[TD]222222222[/TD]
[TD]GBP[/TD]
[TD]1000[/TD]
[TD]20/11/2016[/TD]
[/TR]
[TR]
[TD]444444444[/TD]
[TD]EUR[/TD]
[TD]1200[/TD]
[TD]21/11/2016[/TD]
[/TR]
[TR]
[TD]666666666[/TD]
[TD]USD[/TD]
[TD]999.99[/TD]
[TD]21/11/2016

[/TD]
[/TR]
</tbody>[/TABLE]


I would like to add a VBA code to the first spreadsheet to use a Vlookup to source data from the second Spreasheet to identify where funds have been received.

The second spreadsheet is saved within C:\Users\John\Documents\Payment Report.xlsx

Could someone assist me in how to I add a Vlookup to column E, F and G to return the Currency, Amount and date from the Payment Report. if the Order number is on both reports.

This will also need to go to the last row of data so if you could include this that would be great.

Thanks
Ben

 

[Rijnsent]

Hi Ben,
how far did you get with formulas/recording a macro? Please do post your code here (in [ CODE ] brackets), so we can give you some feedback.
Cheers,
Koen