VBA Code for Bank Recos Closed

Question

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

TrowaD · Answer

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

TrowaD · Answer

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

VBA Code for Bank Recos

2 responses

Similar discussions

Subscribe To Our Newsletter!