Bank Reconciliation Solved/Closed

Question

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

TrowaD · Answer

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

MRafik · Answer

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

TrowaD · Answer

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

MRafik · Answer

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

TrowaD · Answer

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

MRafik · Answer

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

TrowaD · Answer

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

MRafik · Answer

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

TrowaD · Answer

Hi Rafik,

Alright, I get it now. :)

Working on code, won't be able to finish today. :(

Best regards,
Trowa

TrowaD · Answer

Hi Rafik,

Give this code a try:
Sub Reconcile()Dim mMonth, lRow, x As Integer, MySumRec, MySumPay As LongmMonth = InputBox("Enter month number (1-12) for which you want to reconcile")Range("B3").Value = Sheets("Opening Balances").Range("E1").ValuelRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Rec" & mMonth).Range("E2:E" & lRow)    MySumRec = MySumRec + cell.ValueNextRange("B4").Value = MySumReclRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Pay" & mMonth).Range("E2:E" & lRow)    MySumPay = MySumPay + cell.ValueNextRange("B5").Value = MySumPayRange("B9").Value = Range("B6").ValueMySumRec = 0x = mMonthDo    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 - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Opening Balances").Range("K5:K" & lRow)    If cell.Offset(0, -2) = vbNullString Then MySumRec = MySumRec + cell.ValueNextRange("B10").Value = MySumRecMySumPay = 0mMonth = xDo    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 - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Opening Balances").Range("K5:K" & lRow)    If cell.Offset(0, -2) = vbNullString Then MySumPay = MySumPay + cell.ValueNextRange("B11").Value = MySumPayEnd Sub
Let me know how the code works out.

Best regards,
Trowa

MRafik · Answer

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

TrowaD · Answer

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

MRafik · Answer

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

MRafik · Answer

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

TrowaD · Answer

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 LongmMonth = InputBox("Enter month number (1-12) for which you want to reconcile")If mMonth = vbNullString Then Exit SubRange("A26:F" & Rows.Count).ClearContentslRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Rec" & mMonth).Range("E2:E" & lRow)    MySumRec = MySumRec + cell.ValueNextRange("B4").Value = MySumReclRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Pay" & mMonth).Range("E2:E" & lRow)    MySumPay = MySumPay + cell.ValueNextRange("B5").Value = MySumPayRange("B9").Value = Range("B6").ValueMySumRec = 0x = mMonthDo    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 - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("E" & Rows.Count).End(xlUp).RowFor 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 IfNextRange("B10").Value = MySumRecMySumPay = 0mMonth = xDo    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 - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).RowFor 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 IfNextRange("B11").Value = MySumPaymMonth = xIf mMonth = 1 ThenRange("B3").Value = Sheets("Opening Balances").Range("E1").ValueElse: 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 = OBEnd IfEnd Sub
Best regards,
Trowa

MRafik · Answer

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

TrowaD · Answer

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 LongTryAgain:mMonth = Application.InputBox("Enter month number (1-12) for which you want to reconcile")If mMonth = False Then Exit SubIf mMonth < 1 Or mMonth > 12 Then    MsgBox "An invalid entry has been made. Please try again."    GoTo TryAgainEnd IfRange("A26:F" & Rows.Count).ClearContentslRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Rec" & mMonth).Range("E3:E" & lRow)    MySumRec = MySumRec + cell.ValueNextIf IsNumeric(MySumRec) = False Then MySumRec = 0Range("B4").Value = MySumReclRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Pay" & mMonth).Range("E3:E" & lRow)    MySumPay = MySumPay + cell.ValueNextIf IsNumeric(MySumPay) = False Then MySumPay = 0Range("B5").Value = MySumPayRange("B9").Value = Range("B6").ValueMySumRec = 0x = mMonthDo    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 - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("E" & Rows.Count).End(xlUp).RowIf 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    NextEnd IfRange("B10").Value = MySumRecMySumPay = 0mMonth = xDo    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 - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).RowIf 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    NextEnd IfRange("B11").Value = MySumPaymMonth = xIf mMonth = 1 ThenRange("B3").Value = Sheets("Opening Balances").Range("E1").ValueElse: 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 = OBEnd IfEnd Sub

Best regards,
Trowa

MRafik · Answer

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

TrowaD · Answer

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 LongTryAgain: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 SubEnd IfRange("A26:F" & Rows.Count).ClearContentsx = mMonthIf mMonth = 1 ThenRange("B3").Value = Sheets("Opening Balances").Range("E1").ValueElse: 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 = OBEnd IfmMonth = xlRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Rec" & mMonth).Range("E3:E" & lRow) MySumRec = MySumRec + cell.ValueNextIf IsNumeric(MySumRec) = False Then MySumRec = 0Range("B4").Value = MySumReclRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Pay" & mMonth).Range("E3:E" & lRow) MySumPay = MySumPay + cell.ValueNextIf IsNumeric(MySumPay) = False Then MySumPay = 0Range("B5").Value = MySumPayRange("B9").Value = Range("B6").ValueMySumRec = 0Do 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 - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("E" & Rows.Count).End(xlUp).RowIf 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 NextEnd IfRange("B10").Value = MySumRecMySumPay = 0mMonth = xDo 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 - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).RowIf 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 NextEnd IfRange("B11").Value = MySumPayEnd Sub Best regards, Trowa

MRafik · Answer

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

TrowaD · Answer

Hi MRafik,

1) Another good find. To solve this adjust the 2nd code line:
Dim mMonth, lRow, x As Integer, MySumRec, MySumPay, OB As Long
into:
Dim mMonth, lRow, x As Integer, MySumRec, MySumPay, OB As Currency
2) No problem, just insert the following before the last code line (which is End Sub):
lRow = Range("A" & Rows.Count).End(xlUp).RowRange("A26:F" & lRow).Sort Key1:=Range("B26"), Order1:=xlAscending

Best regards,
Trowa

MRafik · Answer

Hi Trowa

Thank you  for all the help.

Everything is working fine except a slight problem.
The problem is when there is no DATE in an uncleared item then everything goes out of zinc. Is it possible to change order to however the uncleared items are firstly on the opening balance sheet and then on Rec01 and Pay01 and so on.

Going one step ahead what would be the best way to use a userform for data entry when batches are involved.

Kind Regards

Rafik

TrowaD · Answer

Hi Rafik,

Not entirely, we can sort on column A first and then on column B. Last code line would then be:
Range("A26:F" & lRow).Sort Key1:=Range("A26"), Key2:=Range("B26"), Order1:=xlAscending 
Opening Balance will then be first, but then all the Pay's and then all the Rec's. 

About the Userform, check out this site which explains nicely how to set up and work with an Userform:
https://www.excel-easy.com/vba/userform.html

Let me know if more guidance is desired.

Best regards,
Trowa

MRafik · Answer

Hi Trowa

Just wondered whether it was passible to use filter, copy and paste in our macro
for the uncleared items in the order opening balance then Rec01 then
Pay01 and so on.
This would avoid the rows going out of zinc.

Kind Regards

Rafik

TrowaD · Answer

Hi Rafik, I think I got it. First I determined at which row the Rec's and Pay's start. Then I used column G (hopefully it is still empty) to put in a formula to determine the number. Then sort on the number and then on the date. Finally remove the formula's from column G. The above is automated in the code below: Sub Reconcile()Dim mMonth, fRow, lRow, x As Integer, MySumRec, MySumPay, OB As CurrencyTryAgain: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 SubEnd IfRange("A26:F" & Rows.Count).ClearContentsx = mMonthIf mMonth = 1 ThenRange("B3").Value = Sheets("Opening Balances").Range("E1").ValueElse: 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 = OBEnd IfmMonth = xlRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Rec" & mMonth).Range("E3:E" & lRow) MySumRec = MySumRec + cell.ValueNextIf IsNumeric(MySumRec) = False Then MySumRec = 0Range("B4").Value = MySumReclRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Pay" & mMonth).Range("E3:E" & lRow) MySumPay = MySumPay + cell.ValueNextIf IsNumeric(MySumPay) = False Then MySumPay = 0Range("B5").Value = MySumPayRange("B9").Value = Range("B6").ValueMySumRec = 0Do 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 - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("E" & Rows.Count).End(xlUp).RowIf 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 NextEnd IfRange("B10").Value = MySumRecMySumPay = 0mMonth = xDo 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 - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).RowIf 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 NextEnd IfRange("B11").Value = MySumPaylRow = Range("A" & Rows.Count).End(xlUp).RowRange("A26:F" & lRow).Sort Key1:=Range("A26"), Key2:=Range("B26"), Order1:=xlAscendingfRow = 25DofRow = fRow + 1Loop Until Range("A" & fRow) <> "Opening Balances"If fRow <> lRow Then For Each cell In Range(Cells(fRow, "A"), Cells(lRow, "A")) cell.Offset(0, 6).Formula = "=right(A" & cell.Row & ",len(A" & cell.Row & ")-3)" Next cell Range(Cells(fRow, "A"), Cells(lRow, "G")).Sort Key1:=Range("G" & fRow), Key2:=Range("B" & fRow), Order1:=xlAscendingEnd IfRange(Cells(fRow, "G"), Cells(lRow, "G")).ClearContentsEnd Sub Best regards, Trowa

MRafik · Answer

Hi Trowa I tried the updated code but still out of zinc I have uploaded a revised file on speedy.share http://speedy.sh/JtdbC/Cash-Book-Rec.xlsm Download at SpeedyShare http://speedy.sh/JtdbC/Cash-Book-Rec.xlsm in which i have highlighted the result of using the filter, copy and paste method manually against using the macro and you will notice the various differences of how they go out of zinc . Hope you can help me solve this. Kind regards Rafik

MRafik · Answer

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

TrowaD · Answer

Hi Rafik, *) It seems I made a fatal mistake in creating the uncleared items list. Fixed it now though. *) Added the condition that the total amount cannot be 0 if it wants to be on the uncleared items list. *) Removed the second filter condition (filter on dates). Try this code: Sub Reconcile()Dim mMonth, fRow, lRow, x As Integer, MySumRec, MySumPay, OB As CurrencyTryAgain: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 SubEnd IfRange("A26:F" & Rows.Count).ClearContentsx = mMonthIf mMonth = 1 ThenRange("B3").Value = Sheets("Opening Balances").Range("E1").ValueElse: 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 = OBEnd IfmMonth = xlRow = Sheets("Rec" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Rec" & mMonth).Range("E3:E" & lRow) MySumRec = MySumRec + cell.ValueNextIf IsNumeric(MySumRec) = False Then MySumRec = 0Range("B4").Value = MySumReclRow = Sheets("Pay" & mMonth).Range("E" & Rows.Count).End(xlUp).RowFor Each cell In Sheets("Pay" & mMonth).Range("E3:E" & lRow) MySumPay = MySumPay + cell.ValueNextIf IsNumeric(MySumPay) = False Then MySumPay = 0Range("B5").Value = MySumPayRange("B9").Value = Range("B6").ValueMySumRec = 0Do 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 And cell <> 0 Then MySumRec = MySumRec + cell.Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Rec" & mMonth Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = cell.Offset(0, -4).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = cell.Offset(0, -3).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = cell.Offset(0, -1).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = "(" & cell.Value & ")" End If Next End If mMonth = mMonth - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("E" & Rows.Count).End(xlUp).RowIf lRow > 4 Then For Each cell In Sheets("Opening Balances").Range("E5:E" & lRow) If cell.Offset(0, -2) = vbNullString And cell <> vbNullString And cell <> 0 Then MySumRec = MySumRec + cell.Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Opening Balances" Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = cell.Offset(0, -4).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = cell.Offset(0, -3).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = cell.Offset(0, -1).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = "(" & cell.Value & ")" End If NextEnd IfRange("B10").Value = MySumRecMySumPay = 0mMonth = xDo 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 And cell <> 0 Then MySumPay = MySumPay + cell.Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Pay" & mMonth Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = cell.Offset(0, -4).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = cell.Offset(0, -3).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = cell.Offset(0, -1).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = cell.Value End If Next End If mMonth = mMonth - 1Loop Until mMonth = 0lRow = Sheets("Opening Balances").Range("K" & Rows.Count).End(xlUp).RowIf lRow > 4 Then For Each cell In Sheets("Opening Balances").Range("K5:K" & lRow) If cell.Offset(0, -2) = vbNullString And cell <> vbNullString And cell <> 0 Then MySumPay = MySumPay + cell.Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Opening Balances" Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = cell.Offset(0, -4).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = cell.Offset(0, -3).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = cell.Offset(0, -1).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = cell.Value End If NextEnd IfRange("B11").Value = MySumPaylRow = Range("A" & Rows.Count).End(xlUp).RowRange("A26:F" & lRow).Sort Key1:=Range("A26"), Order1:=xlAscendingfRow = 25DofRow = fRow + 1Loop Until Range("A" & fRow) <> "Opening Balances"If fRow <> lRow Then For Each cell In Range(Cells(fRow, "A"), Cells(lRow, "A")) cell.Offset(0, 6).Formula = "=right(A" & cell.Row & ",len(A" & cell.Row & ")-3)" Next cell Range(Cells(fRow, "A"), Cells(lRow, "G")).Sort Key1:=Range("G" & fRow), Order1:=xlAscendingEnd IfRange(Cells(fRow, "G"), Cells(lRow, "G")).ClearContentsEnd Sub Best regards, Trowa

MRafik · Answer

Hi Trowa

Thank you for all your help

Everything is working perfectly except one little hitch is that the list of unreconciled items goes from sheet
Opening Balance then 10, then 11, then 12, then 1, 2 ,3 , etc
Is it possible to have the unreconciled list  from sheet Opening Balance and then 1,2,3 and ending at 12.

Once again thank you

Kind regards

Mrafik

TrowaD · Answer

Hi Rafik, Time to handle it a bit differently. First we create a custom list. This isn't difficult and is shown here: https://contexturesblog.com/archives/2010/03/10/sort-it-your-way-with-excel-custom-lists/ Your custom list would look like: Opening Balances Rec1 Pay1 Rec2 Pay2 Rec3 Pay3 Rec4 Pay4 Rec5 Pay5 Rec6 Pay6 Rec7 Pay7 Rec8 Pay8 Rec9 Pay9 Rec10 Pay10 Rec11 Pay11 Rec12 Pay12 Now count at which position your custom list is. To clarify; The "Pens, Paper, File Folders, Staplers"-list is at position 7 in the above link. This number needs to correspond with the number (bold and underlined) in the code below second to last line: Range("A26:F" & lRow).Sort Key1:=Range("A26"), Order1:=xlAscending, OrderCustom:=7 Here is the code: Sub Reconcile() Dim mMonth, fRow, lRow, x As Integer, MySumRec, MySumPay, OB As Currency 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 And cell <> 0 Then MySumRec = MySumRec + cell.Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Rec" & mMonth Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = cell.Offset(0, -4).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = cell.Offset(0, -3).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = cell.Offset(0, -1).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).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 And cell <> 0 Then MySumRec = MySumRec + cell.Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Opening Balances" Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = cell.Offset(0, -4).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = cell.Offset(0, -3).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = cell.Offset(0, -1).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).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 And cell <> 0 Then MySumPay = MySumPay + cell.Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Pay" & mMonth Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = cell.Offset(0, -4).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = cell.Offset(0, -3).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = cell.Offset(0, -1).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).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 And cell <> 0 Then MySumPay = MySumPay + cell.Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Opening Balances" Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = cell.Offset(0, -4).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = cell.Offset(0, -3).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = cell.Offset(0, -1).Value Sheets("Bank Reconciliation").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = cell.Value End If Next End If Range("B11").Value = MySumPay lRow = Range("A" & Rows.Count).End(xlUp).Row Range("A26:F" & lRow).Sort Key1:=Range("A26"), Order1:=xlAscending, OrderCustom:=7 End Sub Best regards, Trowa

MRafik · Answer

Thank You Trowa

Am trying to sort out the userform

Best Regards

Bank Reconciliation

31 responses

Similar discussions

Subscribe To Our Newsletter!