Free Excel training videos

Bank reconciliation

How to perform a Bank Reconciliation using a dowhile loop macro in Excel?
In the last Excel training video we had described what a bank reconciliation is and how to automate the process using a 'for next' loop in a macro. Now we demonstrate the use of a 'do while' loop to automate the bank reconciliation process. This is an elegant and time saving method for any individual or business.
We define the starting row in our cheque book or ledger data in the outer loop and compare it with the bank data which resides in the inner loop. Using the 'IF' function we then compare the cheque numbers and decide what to display in the relevant cells. The complete process of the bank reconciliation takes a few milliseconds. People who have been doing this manually know the difficulties! In fact, we have also calculated the days taken to realize the cheque.



Complete Macro or VBA code:

Sub bankreconciliation()
Dim x, y As Integer
x = 4
y = 4

Do While Cells(x, 2) <> ""
Do While Cells(y, 6) <> ""
If Cells(x, 2) = Cells(y, 6) And Cells(x, 3) = Cells(y, 7) Then
Cells(x, 4) = Cells(y, 6)
End If
y = y + 1
Loop

x = x + 1
y = 4
Loop

End Sub





Further reading and use:
More facts
Bank reconciliation using a 'dowhile' loop macro