VBA code for matching two excel files and multiple sheets (urgent)

[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. If you want a sample file, please let me know. But how can I upload one here?

Following is my simple VBA that I use now

In test file 2.xlsm

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Call Script1(Target)
Call Script2(Target)
Call Script3(Target)
End Sub


Private Sub Script1(ByVal Target As Range)
On Error GoTo ErrHnd:
If Target.Column = 10 Then

'disable events to stop changes made by this macro re-trigering it
Application.EnableEvents = False
If Not IsDate(Range("A" & Target.Row).Value) Then
Range("A" & Target.Row).Value = Format(Date, "dd/mmm/yy")
End If
're-enable events
Application.EnableEvents = True
End If
Exit Sub


'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub


Private Sub Script2(ByVal Target As Range)
Dim c As Range
If Intersect(Range("I:L"), Target) Is Nothing Then Exit Sub
For Each c In Intersect(Range("J:M"), Target)
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(c.Row, "P").Value = Environ("username")
Application.EnableEvents = True
End If
Next c
End Sub


Private Sub Script3(ByVal Target As Range)
Dim c As Range
If Intersect(Range("I:L"), Target) Is Nothing Then Exit Sub
For Each c In Intersect(Range("J:M"), Target)
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(c.Row, "Q").Value = Format(Now, "DD-MMM-YY HH:MM:SS")
Application.EnableEvents = True
End If
Next c
End Sub


In test file 1.xlsm

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Call Script1(Target)
Call Script2(Target)
Call Script3(Target)
End Sub


Private Sub Script1(ByVal Target As Range)
On Error GoTo ErrHnd:
If Target.Column = 6 Then

'disable events to stop changes made by this macro re-trigering it
Application.EnableEvents = False
If Not IsDate(Range("A" & Target.Row).Value) Then
Range("A" & Target.Row).Value = Format(Date, "dd/mmm/yy")
End If
're-enable events
Application.EnableEvents = True
End If
Exit Sub


'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub


Private Sub Script2(ByVal Target As Range)
Dim c As Range
If Intersect(Range("F:G"), Target) Is Nothing Then Exit Sub
For Each c In Intersect(Range("F:G"), Target)
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(c.Row, "Q").Value = Environ("username")
Application.EnableEvents = True
End If
Next c
End Sub


Private Sub Script3(ByVal Target As Range)
Dim c As Range
If Intersect(Range("F:G"), Target) Is Nothing Then Exit Sub
For Each c In Intersect(Range("F:G"), Target)
If Not IsEmpty(c) Then
Application.EnableEvents = False
Cells(c.Row, "R").Value = Format(Now, "DD-MMM-YY HH:MM:SS")
Application.EnableEvents = True
End If
Next c
End Sub

 

[spearhead]

Here are the links to the sample files

test file 1.xlsm - https://www.dropbox.com/s/n5rr7swdsk6vb9y/test file 1.xlsm?dl=0
test file 2.xlsm - https://www.dropbox.com/s/io1xy59mmgab643/test file 2.xlsm?dl=0

 

[spearhead]

Please anyone help me.

 

[spearhead]

Any help?

 

[spearhead]

Please can anyone help me....

 

[spearhead]

Please anyone?