Bank Reconciliation [Solved/Closed]

Report
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013
-
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
-
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

31 replies

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013

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
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013

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
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013

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
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013

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
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
Hi Rafik,

Alright, I get it now. :)

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

Best regards,
Trowa
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013

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
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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

Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013

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
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013

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
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013

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
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013

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
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
Posts
24
Registration date
Wednesday January 9, 2013
Status
Member
Last seen
November 25, 2013

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