VBA code for matching two excel files and multiple sheets

[spearhead]

Guys,

I am in need of big help. My boss has told me to prepare a comprehensive report to be sent to the management every month. I have searched everywhere for two days without much luck on doing the following to complete my report. Following are the issues I am trying to find a solution for. Since I am a newbie when it comes to VBA codes, so please can you help me with the following. I have some VBA codes already, so therefore please provide me the codes for the following to go with the ones i already have.

My requirement is to,

1) A VBA code to match the values in column "D" in sheet "BDS Download List" with the values in column "J-M" in sheet "Received Mandates". If a match is found in either of column J, K, L, M, a remark should be updated in column "E" as "Received" in sheet "BDS Download List".
Columns J, K, L and M will sometimes have the same value in several places, therefore the matching should be considering the latest date. The match result should be displayed in corresponding cell (same row). This is in test file 2.xlsm
Eg: if no. 12345 is available in column "J" on 20.11.2015 and again the same no. in column "L" on 28.11.2015, the VBA code should only match the 28.11.2015 record.


2) A VBA code to update value "RTN" in column "D" in sheet "Received Mandates" (test file 2.xlsm) if the no. entered in columns "F" or "G" (in "Return Mandates" sheet in excel file test file 1.xlsm) matches the no. available in either Columns J, K, L or M (in "Received Mandates" sheet excel file test file 2.xlsm). The match result should be displayed in corresponding cell (same row). Same as above the no. should be the latest entered no.
Eg: if test file 1.xlsm ("Return Mandates" sheet column "F" or "G") file has no. 12345 entered on 28.11.2015, and test file 2.xlsm ("Received Mandates" sheet column "J", "K", "L" or "M" has 12345 on 20.11.2015 and 27.11.2015, the "RTN" value should be updated on 27.11.2015 record row.

3) The code requested in point 1 should check whether there is a "RTN" value in column "D" in sheet "Received Mandates" (test file 2.xlsm) if the value is available, the value returned in point 1 should be "Not Received" if no "RTN" value the value returned should be "Received"

4) If any of the manually entered figures are deleted by the user which is required for the VBA code processing, the value automatically updated from the VBA code should also be deleted by the Code.

5) I am getting a runtime error '424' object not found in column "I" sheet "Received Mandates" (test file 2.xlsm). Please tell me what to do for this too.

Hope you can help me on this. Sorry for all the trouble. And thank everyone of you in advance. Attached the sample files for your reference.