Bank Reconciliation Using Excel VBA

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

View the training video:


Further reading:
Bank Reconciliation

2 thoughts on “Bank Reconciliation Using Excel VBA

  1. Pingback: How to Import Data from Word Tables into Excel with VBA | Excel VBA Training Videos

  2. sharon b. millan

    Hi,

    Thank you for this tutorial, I am trying apply this approach to google sheets. Do you have a code to run exactly the same code for google sheets? thanks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *