## 31 replies

TrowaD

- Posts
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

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

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

MRafik

- 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

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

TrowaD

- Posts
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

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

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

MRafik

- 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

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

TrowaD

- Posts
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

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

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

MRafik

- 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

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
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

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

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

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
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

Hi Rafik,

Alright, I get it now. :)

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

Best regards,

Trowa

Alright, I get it now. :)

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

Best regards,

Trowa

- Posts
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

Hi Rafik,

Give this code a try:

Best regards,

Trowa

Give this code a try:

Sub Reconcile()Let me know how the code works out.

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

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

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
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

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

Best regards,

Trowa

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

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

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
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

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:

Trowa

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()Best regards,

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

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

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
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

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:

Best regards,

Trowa

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

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
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

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:

Trowa

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()Best regards,

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

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

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