VBA Code for Bank Recos

[Closed]
Report
Posts
1
Registration date
Wednesday April 8, 2015
Status
Member
Last seen
April 8, 2015
-
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
-
Hi All,

Greetings

I am looking for a VBA code for bank recons where I want to reconcile cash & bank statements, where i want to move match items to sheet-2 and unmatched items to sheet3.

Please please someone help me here it's urgent.

Cash Book Bank Book

Date Reference Amount Date Reference Amount
1-Apr 3001 200 4-Apr 3003 500
2-Apr 3002 300 6-Apr 3001 200
3-Apr 3003 500 1-Apr 3005 600
4-Apr 3004 100 3-Apr 3002 300
5-Apr 3005 600 2-Apr 3004 100
6-Apr 3006 700 5-Apr 3006 700

Regards,

Khaja

2 replies

Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
483
Hi Khaja,

Can a match only occur on the same row?

After your sample data has been processed will Sheet2 have 1 row?:
6-Apr 3006 700 5-Apr 3006 700
or 2 rows?:
6-Apr 3006 700
5-Apr 3006 700

So how does your sample data look like once it has been VBA'ed?

Best regards,
Trowa
I want my match data in 2 rows.
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
483
Hi Moizuddin,

Try the code below and see if it meets your requirements:
Sub RunMe()
Dim lRow, x As Integer

Sheets("Sheet1").Select

lRow = Range("A1").End(xlDown).Row

Do
    x = x + 1
    If Cells(x, "B") & Cells(x, "C") = Cells(x, "E") & Cells(x, "F") Then
        Range(Cells(x, "A"), Cells(x, "C")).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Range(Cells(x, "D"), Cells(x, "F")).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Else
        Range(Cells(x, "A"), Cells(x, "C")).Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Range(Cells(x, "D"), Cells(x, "F")).Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Loop Until x = lRow
End Sub


Best regards,
Trowa
Thanks dear for your effort. The code is working partially, i mean it's pasting the matched items in sheets2, but i want the matched items together in sheet2 for example i want my matched data in A1 & A2 , A3 & A4 so on. I think the code for sheet3 is not working, because its showing all the entries in sheet3, whereas i want only the unmatched items either from cash book or bank book in sheet3.

Please help me here
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
483
Hi Moiz,

So you want to put the Date, Reference and Amount all in one cell?

You didn't answer my question from earlier:
Can a match only occur on the same row?
But if the answer is no then all the data has a match, right? So I assumed the answer would be yes.
This would mean that only the row with reference 3006 would go to sheet2 and the rest (as being data without a match) would go to sheet3.

So let me ask another question you didn't answer:
So how does your sample data look like once it has been VBA'ed (processed by VBA)?

Please clear the confusion by answering all the questions.

Best regards,
Trowa