Previous Topic Next Topic

VBA Code for Bank Recos

Closed
moizuddin9 Posts 1 Registration date Wednesday April 8, 2015 Status Member Last seen April 8, 2015 - Apr 8, 2015 at 02:52 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 14, 2015 at 10:54 AM
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
Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 9, 2015 at 11:18 AM
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
0
moizuddin
Apr 12, 2015 at 12:37 AM
I want my match data in 2 rows.
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 13, 2015 at 12:06 PM
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
0
moiz
Apr 14, 2015 at 05:15 AM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 14, 2015 at 10:54 AM
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
0

Similar discussions

Unlock code
Priya -
HelpiOS -

1 response
lava hard reset & factory reset
AVAY -
Har -

3 responses
unlock code for nokia 105 keypad. For tetris game
HtetThiri -
HtetThiri -

0 responses