Previous Topic Next Topic

Bank Reconciliation

Solved/Closed
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013 - Aug 23, 2013 at 02:11 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 26, 2013 at 11:00 AM
Hi
Am trying to automate bank reconciliation
Any ideas as what the code is.
In the work book i have 12 worksheets for receipts
and 12 worksheets for payments. 1 work sheet for
Bank reconciliation and 1 worksheet for opening balances.
A button on Bank recociliation worksheet were i need to put this code in.
The 12 worksheets will be called Rec 1 Rec 2 etc and the other 12
will be called pay 1 , pay 2 etc
There will be a column in the above worksheets which will be for marking
which items have been cleared.
Any help will be appreciated
Regards
Related:

31 responses

Answer 1 / 31
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 26, 2013 at 11:05 AM
Hi MRafik,

So you want to loop through the 12 rec and the 12 pay sheets looking for marking in column (What kind of mark? and which column?) and then move the unmarked rows to the Bank reconciliation sheet?

Am I on the right track?

Best regards,
Trowa
0
Answer 2 / 31
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013
Aug 27, 2013 at 05:52 AM
Hi Trowa

Yes you are on the right track.
Will also need to take account of the opening balance and
check the uncleared items on the opening balance sheet.

The mark will be between 1 and 12.
Column C will be the one to mark in.

Column A - Date
Column B - Details
Column D - Reference
Column E - Total Amount
Column F - Amount

On clicking the button BANK REC an input box pops up and
asks the question WHICH MONTH YOU WANT TO PREPARE
RECONCILIATION FOR?

Following will be on the reconciliation sheet
Cash book summary:
Opening balance for the month
Plus receipts in the month
Less expenditure in the month
Closing balance

Reconciliation to bank:
Cash book balance as above
Less uncleared receipts below
Plus uncleared payments below
Balance per bank statement

Regards

Rafik
0
Answer 3 / 31
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 27, 2013 at 10:35 AM
Hi MRafik,

For me to write a code, I need to know when (click button?) which info (columns A:F?) needs to go where (?).

This is not clear to me, so let me ask you to upload (a sample of) your file using a file sharing site like www.speedyshare or ge.tt (careful with personal info).

Best regards,
Trowa
0
Answer 4 / 31
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013
Aug 29, 2013 at 10:29 AM
Hi Trowa

Just uploaded a sample file on speedyshare

The link is
[code]http://speedy.sh/B4B4x/Cash-Book-Rec.xlsm[/code]

http://speedy.sh/B4B4x/Cash-Book-Rec.xlsm

Regards

MRafik
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 31
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 2, 2013 at 10:37 AM
Hi MRafik,

So depending on the number entered when clicking the Reconcile button on the Bank Reconciliation sheet, you want to check the respective Rec and Pay sheets.

Then you want to do some math on the Bank Reconciliation sheet.
Could you clearly explain what math (and/or which data needs to be pulled) needs to be done?

Best regards,
Trowa
0
Answer 6 / 31
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013
Sep 2, 2013 at 02:10 PM
Hi Trowa

Say year starts in Jan and ends in Dec so the numbers will be 1 to 12
The opening balance for Jan will be what we enter in the sheet opening balance
E1 which will equal to Sheet Bank Reconciliation B3

Totals if month 1 is entered will be total of Rec01
Sheet Bank Reconciliation = B4
Total of Pay01 =B5

B6 = SUM(B3:B5)

Data that needs to be pulled will be any amounts in Column E which have no
corresponding entry in column C in both Rec01 and Pay01
and also any amounts in column E which have no corresponding entry in column C and column K which have no corresponding entry in column I in Worksheet opening Balances
Totals of which will go in B10 (Receipts) and B11 (Payment) on worksheet
bank reconciliation.

If month 2 is selected then opening balance for month 2 should be shown as opening balance. Totals for Rec02 and Pay02 should be shown and all data
as above should be pulled from Opening balance Worksheet , Rec01,Rec02,
Pay01 and Pay02.

Hope this clarifies.

Regards

Rafik
0
Answer 7 / 31
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 3, 2013 at 11:32 AM
Hi Rafik,

If month 1 is entered
On sheet Bank Reconciliation:
B3: ='Opening Balances'!E1
B4: Sum of column E if C is empty of Rec01 sheet
B5: Sum of column E if C is empty of Pay01 sheet

B9: ?
B10: Sum of column E if C is empty of Rec01 sheet +
Sum of column K if I is empty of Opening Balance sheet
B11: Sum of column E if C is empty of Pay01 sheet +
Sum of column K if I is empty of Opening Balance sheet


If month 3 is entered
On sheet Bank Reconciliation:
B3: ='Opening Balances'!E1
B4: Sum of column E if C is empty of Rec03 sheet
B5: Sum of column E if C is empty of Pay03 sheet

B9: ?
B10: Sum of column E if C is empty of Rec01 sheet +
Sum of column E if C is empty of Rec02 sheet +
Sum of column E if C is empty of Rec03 sheet +
Sum of column K if I is empty of Opening Balance sheet
B11: Sum of column E if C is empty of Pay01 sheet +
Sum of column E if C is empty of Pay02 sheet +
Sum of column E if C is empty of Pay03 sheet +
Sum of column K if I is empty of Opening Balance sheet

As you can see I interpreted "Corresponding entry" as empty.

B6 and B12 are SUM formula's but you didn't mention B9.

Curious to see if this is what you meant?

Best regards,
Trowa
0
Answer 8 / 31
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013
Sep 3, 2013 at 12:44 PM
Hi Trowa

Few amendments

If month 1 is entered
On sheet Bank Reconciliation:
B3: ='Opening Balances'!E1
B4: Sum of column E of Rec01 sheet
B5: Sum of column E of Pay01 sheet

B9 = B6
B10: Sum of column E if C is empty of Rec01 sheet +
Sum of column K if I is empty of Opening Balance sheet
B11: Sum of column E if C is empty of Pay01 sheet +
Sum of column K if I is empty of Opening Balance sheet

These will be listed on the bank reconciliation page marked as uncleared.


If month 3 is entered
On sheet Bank Reconciliation:
B3: ='Opening Balances from end of Month 2
B4: Sum of column E of Rec03 sheet
B5: Sum of column E of Pay03 sheet

B9 = B6
B10: Sum of column E if C is empty of Rec01 sheet +
Sum of column E if C is empty of Rec02 sheet +
Sum of column E if C is empty of Rec03 sheet +
Sum of column K if I is empty of Opening Balance sheet
B11: Sum of column E if C is empty of Pay01 sheet +
Sum of column E if C is empty of Pay02 sheet +
Sum of column E if C is empty of Pay03 sheet +
Sum of column K if I is empty of Opening Balance sheet

Your interpretation is correct

Hope the above clarifies

Best regards,

Rafik
0
Answer 9 / 31
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 5, 2013 at 11:35 AM
Hi Rafik,

Alright, I get it now. :)

Working on code, won't be able to finish today. :(

Best regards,
Trowa
0
Answer 10 / 31
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 9, 2013 at 10:13 AM
Hi Rafik,

Give this code a try: 
Sub Reconcile()
Dim mMonth, lRow, x As Integer, MySumRec, MySumPay As Long

mMonth = InputBox("Enter month number (1-12) for which you want to reconcile")

Range("B3").Value = Sheets("Opening Balances").Range("E1").Value

lRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Rec" & mMonth).Range("E2:E" & lRow)
    MySumRec = MySumRec + cell.Value
Next

Range("B4").Value = MySumRec

lRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Pay" & mMonth).Range("E2:E" & lRow)
    MySumPay = MySumPay + cell.Value
Next

Range("B5").Value = MySumPay
Range("B9").Value = Range("B6").Value

MySumRec = 0
x = mMonth
Do
    lRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
    For Each cell In Sheets("Rec" & mMonth).Range("E2:E" & lRow)
        If cell.Offset(0, -2) = vbNullString Then MySumRec = MySumRec + cell.Value
    Next
    mMonth = mMonth - 1
Loop Until mMonth = 0

lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Opening Balances").Range("K5:K" & lRow)
    If cell.Offset(0, -2) = vbNullString Then MySumRec = MySumRec + cell.Value
Next

Range("B10").Value = MySumRec

MySumPay = 0
mMonth = x
Do
    lRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
    For Each cell In Sheets("Pay" & mMonth).Range("E2:E" & lRow)
        If cell.Offset(0, -2) = vbNullString Then MySumPay = MySumPay + cell.Value
    Next
    mMonth = mMonth - 1
Loop Until mMonth = 0

lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Opening Balances").Range("K5:K" & lRow)
    If cell.Offset(0, -2) = vbNullString Then MySumPay = MySumPay + cell.Value
Next

Range("B11").Value = MySumPay

End Sub
Let me know how the code works out.

Best regards,
Trowa
0
Answer 11 / 31
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013
Sep 16, 2013 at 09:37 AM
Thank you Trowa

Tried the code but get a few problems.
It seems to work for the first month but when any other month
is entered then the opening balance does not change and giver
error 13 and stops at line
MySumPay = MySumPay + cell.value

Also it does not give a list of uncleared items on the reconciliation page.

Finally for some odd reason uncleared items on the opening balance page
are added on both unleared receipts and uncleared payments although
the result is correct

Hope you can help

Regards

Rafik
0
Answer 12 / 31
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 17, 2013 at 11:22 AM
Hi Rafik,

1. When I test the code I didn't get the error. Let me post your sample file with code, so you can see if there is a difference.

2. The list of uncleared items had been lost, trying to figure out which calculations had to be done. It's implemented now.

3. OK, I just noticed that opening balance sheet has a rec and pay part. I guess the numbers were just right to give a correct result. Adjusted the code.

Here is your file:
http://speedy.sh/fRbYk/MRafik-Cash-Book-Rec.xls
NOTE that on the bank reconciliation sheet: A25, B25, C25, E25, F25 contains a 1 (random data) to determine the start of the uncleared list.

Best regards,
Trowa

0
Answer 13 / 31
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013
Sep 19, 2013 at 10:06 AM
Thanks Trowa

Yes the error is now gone.

Couple of little problems

1) The opening balance does not change.
It needs to change according to the number being put in the input box.

So if 1 then opening balance will be Sheet opening balance cell E1

If 2 then Above amount plus total of Sheet Rec01 Column E minus Sheet
Pay01 Column E

If 3 then either balance of above plus total of Sheet Rec02 Column E minus
Sheet Pay02 Column E OR Opening Balance plus Sheet Rec01 and Rec02 Columns E minus Sheet Pay01 and Pay02 Columns E

2) The list of uncleared items headed AMOUNT is wrong .
Sorry my mistake should have read TOTAL AMOUNT.
And the list does not include uncleared items from Sheet Opening balances.


Kind regards

Rafik
0
Answer 14 / 31
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013
Sep 20, 2013 at 11:57 AM
Hi Trowa

Sorry forgot to add on 1) and so on.So every time a new number is put
in the input box the opening balance should change accordingly.

And on 2) forgot to add that the uncleared items of receipts will be negatives
ie have brackets().

Thank you

Kind Regards

Rafik
0
Answer 15 / 31
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 23, 2013 at 11:10 AM
Hi Rafik,

1) Adjusted the code. Not sure what you mean by you last post, but Opening balance will be determined each time the code is run according to your sep 19 post.

2a) List of uncleared items will now take the Total Amount instead of Amount.

2b) Uncleared items of the opening balance sheet are added (althought I noticed that the opening balance sheet doesn't have a Total Amount).

2c) Uncleared items of receipts will be treated as negatives. Go to Cell Properties to choose the right way to display these negative.

Here is the code: 
Sub Reconcile()
Dim mMonth, lRow, x As Integer, MySumRec, MySumPay, OB As Long

mMonth = InputBox("Enter month number (1-12) for which you want to reconcile")

If mMonth = vbNullString Then Exit Sub

Range("A26:F" & Rows.Count).ClearContents

lRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Rec" & mMonth).Range("E2:E" & lRow)
    MySumRec = MySumRec + cell.Value
Next

Range("B4").Value = MySumRec

lRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Pay" & mMonth).Range("E2:E" & lRow)
    MySumPay = MySumPay + cell.Value
Next

Range("B5").Value = MySumPay
Range("B9").Value = Range("B6").Value

MySumRec = 0
x = mMonth
Do
    lRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
    For Each cell In Sheets("Rec" & mMonth).Range("E2:E" & lRow)
        If cell.Offset(0, -2) = vbNullString Then
            MySumRec = MySumRec + cell.Value
        Else
            Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Rec" & mMonth
            Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
            Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
            Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
            Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = "(" & cell.Value & ")"
        End If

    Next
    mMonth = mMonth - 1
Loop Until mMonth = 0

lRow = Sheets("Opening Balances").Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Opening Balances").Range("E5:E" & lRow)
    If cell.Offset(0, -2) = vbNullString Then
        MySumRec = MySumRec + cell.Value
    Else
        Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Opening Balances"
        Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
        Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
        Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
        Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = "(" & cell.Value & ")"
    End If
Next

Range("B10").Value = MySumRec

MySumPay = 0
mMonth = x
Do
    lRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
    For Each cell In Sheets("Pay" & mMonth).Range("E2:E" & lRow)
        If cell.Offset(0, -2) = vbNullString Then
            MySumPay = MySumPay + cell.Value
        Else
            Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Pay" & mMonth
            Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
            Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
            Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
            Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Value
        End If
    Next
    mMonth = mMonth - 1
Loop Until mMonth = 0

lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Opening Balances").Range("K5:K" & lRow)
    If cell.Offset(0, -2) = vbNullString Then
        MySumPay = MySumPay + cell.Value
    Else
        Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Opening Balances"
        Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
        Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
        Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
        Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Value
    End If
Next

Range("B11").Value = MySumPay

mMonth = x
If mMonth = 1 Then
Range("B3").Value = Sheets("Opening Balances").Range("E1").Value
Else: OB = Sheets("Opening Balances").Range("E1").Value
    Do
        mMonth = mMonth - 1
        lRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
        For Each cell In Sheets("Rec" & mMonth).Range("E2:E" & lRow)
                OB = OB + cell.Value
        Next
    Loop Until mMonth = 1
    mMonth = x
    Do
        mMonth = mMonth - 1
        lRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
        For Each cell In Sheets("Pay" & mMonth).Range("E2:E" & lRow)
                OB = OB - cell.Value
        Next
    Loop Until mMonth = 1
    Range("B3").Value = OB
End If

End Sub
Best regards,
Trowa
0
Answer 16 / 31
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013
Sep 26, 2013 at 08:56 AM
Hi Trowa

Sorry to trouble you once again.

Have the following issues.

1) When any number apart from 1 to 12 is entered eg 0 then it gives a runtime error 9. Is it possible to restrict user to input only numbers between 1 and 12.

2) When user inputs 1 and there is no entries in Rec1 and Pay1 then on the bank reconciliation page Add receipts and Add payments show Total Amount and closing Balance shows # Value rather then 0.

3) The list of uncleared items shows cleared items rather then the other way round.

Thank you

Regards

Rafik
0
Answer 17 / 31
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 26, 2013 at 11:11 AM
Hi Rafik,

No trouble at all, it's actually good to see you test the code in various ways.

All issues have been addressed. So test away with the following code: 
Sub Reconcile()
Dim mMonth, lRow, x As Integer, MySumRec, MySumPay, OB As Long

TryAgain:
mMonth = Application.InputBox("Enter month number (1-12) for which you want to reconcile")

If mMonth = False Then Exit Sub
If mMonth < 1 Or mMonth > 12 Then
    MsgBox "An invalid entry has been made. Please try again."
    GoTo TryAgain
End If

Range("A26:F" & Rows.Count).ClearContents

lRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Rec" & mMonth).Range("E3:E" & lRow)
    MySumRec = MySumRec + cell.Value
Next

If IsNumeric(MySumRec) = False Then MySumRec = 0
Range("B4").Value = MySumRec

lRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Pay" & mMonth).Range("E3:E" & lRow)
    MySumPay = MySumPay + cell.Value
Next

If IsNumeric(MySumPay) = False Then MySumPay = 0
Range("B5").Value = MySumPay

Range("B9").Value = Range("B6").Value

MySumRec = 0
x = mMonth
Do
    lRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
    If lRow > 2 Then
        For Each cell In Sheets("Rec" & mMonth).Range("E3:E" & lRow)
            If cell.Offset(0, -2) = vbNullString Then
                MySumRec = MySumRec + cell.Value
                Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Rec" & mMonth
                Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
                Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
                Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
                Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = "(" & cell.Value & ")"
            End If
    
        Next
    End If
    mMonth = mMonth - 1
Loop Until mMonth = 0

lRow = Sheets("Opening Balances").Range("E" & Rows.Count).End(xlUp).Row
If lRow > 4 Then
    For Each cell In Sheets("Opening Balances").Range("E5:E" & lRow)
        If cell.Offset(0, -2) = vbNullString Then
            MySumRec = MySumRec + cell.Value
            Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Opening Balances"
            Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
            Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
            Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
            Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = "(" & cell.Value & ")"
        End If
    Next
End If

Range("B10").Value = MySumRec

MySumPay = 0
mMonth = x
Do
    lRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
    If lRow > 2 Then
        For Each cell In Sheets("Pay" & mMonth).Range("E3:E" & lRow)
            If cell.Offset(0, -2) = vbNullString Then
                MySumPay = MySumPay + cell.Value
                Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Pay" & mMonth
                Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
                Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
                Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
                Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Value
            End If
        Next
    End If
    mMonth = mMonth - 1
Loop Until mMonth = 0

lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).Row
If lRow > 4 Then
    For Each cell In Sheets("Opening Balances").Range("K5:K" & lRow)
        If cell.Offset(0, -2) = vbNullString Then
            MySumPay = MySumPay + cell.Value
            Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Opening Balances"
            Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
            Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
            Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
            Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Value
        End If
    Next
End If

Range("B11").Value = MySumPay

mMonth = x
If mMonth = 1 Then
Range("B3").Value = Sheets("Opening Balances").Range("E1").Value
Else: OB = Sheets("Opening Balances").Range("E1").Value
    Do
        mMonth = mMonth - 1
        lRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
        If lRow > 2 Then
            For Each cell In Sheets("Rec" & mMonth).Range("E3:E" & lRow)
                    OB = OB + cell.Value
            Next
        End If
    Loop Until mMonth = 1
    
    mMonth = x
    Do
        mMonth = mMonth - 1
        lRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
        If lRow > 2 Then
            For Each cell In Sheets("Pay" & mMonth).Range("E3:E" & lRow)
                    OB = OB - cell.Value
            Next
        End If
    Loop Until mMonth = 1
    
    Range("B3").Value = OB
End If

End Sub

Best regards,
Trowa
0
Answer 18 / 31
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013
Oct 1, 2013 at 10:07 AM
Hi Trowa

Once again thank you.

Few minor problems

!) When a 0 (zero) is entered in the input box nothing happens, need to get message box to pop up .

2) There seems to be a delayed reaction on the bank reconciliation page in cell B9 as it does not change on the first click but does so later. Eg when i input an amount in the opening balance sheet in E1 and try and reconcile the figures in cell B3 changes but B9 does not.

3) The uncleared items list includes all rows with blanks in column C of Rec and Pay sheets but i need to list only those rows with amounts in column E (This is because a lot of entries are batch entries)

Regards

Rafik
0
Answer 19 / 31
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 1, 2013 at 11:40 AM
Hi Rafik,

1) Hope you like the new way I handled wrong entry's.

2) Good find; adjusted code.

3) This wasn't clear yet; adjusted code.

And here is the code: 
Sub Reconcile()
Dim mMonth, lRow, x As Integer, MySumRec, MySumPay, OB As Long

TryAgain:
mMonth = Application.InputBox("Enter month number (1-12) for which you want to reconcile")

If mMonth = False Or mMonth = vbNullString Or mMonth < 1 Or mMonth > 12 Then
    MsgBox "No valid entry has been made, you either:" & Chr(10) & _
    "- Input a 0" & Chr(10) & "- Input nothing" & Chr(10) & "- Hit the cancel button" _
    & Chr(10) & "- Input a number smaller then 1 or bigger then 12", , "Invalid entry"
    z = MsgBox("Would you like to try again?", vbYesNo)
    If z = vbYes Then GoTo TryAgain
    Exit Sub
End If

Range("A26:F" & Rows.Count).ClearContents

x = mMonth

If mMonth = 1 Then
Range("B3").Value = Sheets("Opening Balances").Range("E1").Value
Else: OB = Sheets("Opening Balances").Range("E1").Value
    Do
        mMonth = mMonth - 1
        lRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
        If lRow > 2 Then
            For Each cell In Sheets("Rec" & mMonth).Range("E3:E" & lRow)
                    OB = OB + cell.Value
            Next
        End If
    Loop Until mMonth = 1
    
    mMonth = x
    Do
        mMonth = mMonth - 1
        lRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
        If lRow > 2 Then
            For Each cell In Sheets("Pay" & mMonth).Range("E3:E" & lRow)
                    OB = OB - cell.Value
            Next
        End If
    Loop Until mMonth = 1
    
    Range("B3").Value = OB
End If

mMonth = x

lRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Rec" & mMonth).Range("E3:E" & lRow)
    MySumRec = MySumRec + cell.Value
Next

If IsNumeric(MySumRec) = False Then MySumRec = 0
Range("B4").Value = MySumRec

lRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("Pay" & mMonth).Range("E3:E" & lRow)
    MySumPay = MySumPay + cell.Value
Next

If IsNumeric(MySumPay) = False Then MySumPay = 0
Range("B5").Value = MySumPay

Range("B9").Value = Range("B6").Value

MySumRec = 0
Do
    lRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
    If lRow > 2 Then
        For Each cell In Sheets("Rec" & mMonth).Range("E3:E" & lRow)
            If cell.Offset(0, -2) = vbNullString And cell <> vbNullString Then
                MySumRec = MySumRec + cell.Value
                Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Rec" & mMonth
                Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
                Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
                Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
                Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = "(" & cell.Value & ")"
            End If
    
        Next
    End If
    mMonth = mMonth - 1
Loop Until mMonth = 0

lRow = Sheets("Opening Balances").Range("E" & Rows.Count).End(xlUp).Row
If lRow > 4 Then
    For Each cell In Sheets("Opening Balances").Range("E5:E" & lRow)
        If cell.Offset(0, -2) = vbNullString And cell <> vbNullString Then
            MySumRec = MySumRec + cell.Value
            Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Opening Balances"
            Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
            Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
            Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
            Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = "(" & cell.Value & ")"
        End If
    Next
End If

Range("B10").Value = MySumRec

MySumPay = 0
mMonth = x
Do
    lRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).Row
    If lRow > 2 Then
        For Each cell In Sheets("Pay" & mMonth).Range("E3:E" & lRow)
            If cell.Offset(0, -2) = vbNullString And cell <> vbNullString Then
                MySumPay = MySumPay + cell.Value
                Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Pay" & mMonth
                Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
                Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
                Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
                Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Value
            End If
        Next
    End If
    mMonth = mMonth - 1
Loop Until mMonth = 0

lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).Row
If lRow > 4 Then
    For Each cell In Sheets("Opening Balances").Range("K5:K" & lRow)
        If cell.Offset(0, -2) = vbNullString And cell <> vbNullString Then
            MySumPay = MySumPay + cell.Value
            Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Opening Balances"
            Sheets("Bank Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -4).Value
            Sheets("Bank Reconciliation").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -3).Value
            Sheets("Bank Reconciliation").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Offset(0, -1).Value
            Sheets("Bank Reconciliation").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Value
        End If
    Next
End If

Range("B11").Value = MySumPay

End Sub
Best regards,
Trowa
0
Answer 20 / 31
MRafik Posts 24 Registration date Wednesday January 9, 2013 Status Member Last seen November 25, 2013
Oct 7, 2013 at 09:18 AM
Hi Trowa

Have been testing the code but seem to be getting errors on the opening balance which is being brought forward each month.

I am uploading a file on speedy share with some test data which gives me the following differences

Opening balance differeces
From Month 1 to Month 2 +3.39
From Month 2 to Month 3 +.81
From Month 3 to Month 4 -2.49
From Month 4 to Month 5 +3.85

[code]http://speedy.sh/CBvkj/Cash-Book-Rec.xlsm/code

http://speedy.sh/CBvkj/Cash-Book-Rec.xlsm


Think the problem is to do with rounding off as the balances brought forward are always whole numbers.


Is it also possible to have the uncleared items listed in date order oldest being first

Thank you

Kind regards
0
  • 1
  • 2

Similar discussions

Tally not responding during reconciliation
Akashpatil -
ac3mark -

2 responses