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

spearhead

New Member
Joined
Nov 30, 2015
Messages
48
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
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top