Free Excel training videos

Bank reconciliation

What Is a Bank Reconciliation?
It is the process of comparing and matching figures from our accounting records against those presented on a bank statement. The aim is to discover and correct any possible errors and inform the bank immediately if they have made a mistake or correct the discrepancy at our end. Timing differences are inevitable since you might have issued a cheque on the 1st October 2009 and it gets cleared on the 5th October 2009 by the bank. Performing a bank reconciliation manually is time consuming and error prone. Excel provides an easy and quick method to perform the process. We demonstrate the use of a nested 'for... next' loop to implement bank reconciliaton.
First we define the starting row of the external loop. The first data in our records is then compared with all the records of the bank in the internal loop. Then the macro goes to the next record in the external loop and again compares all the bank reords in the internal loop. If the cheque number and the amount in our records matches those of the bank the macro diplays "Matches!" in the specified worksheet cell.

Macro code:
Sub bankreconciliation()
Dim x, y As Integer

For x = 3 To 8
For y = 3 To 8
If Cells(x, 2) = Cells(y, 6) And Cells(x, 3) = Cells(y, 7) Then
Cells(x, 4) = Cells(y, 6)
End If
Next y
Next x

End Sub

Further reading and use:
More facts
Bank reconciliation