Code to Customize Bank statement

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello Experts

I have this code which customizes bank statement as I want it to be. This code works for one particular bank only. AS there are N number of banks and the format and heading of each bank is different, I am not able to edit the code accordingly. AS the code was shared without comments, I don’t have any idea what some of the lines in the code actually do. The code in the VBA editor window works for the bank hidden in the workbook only. There is another code which is to be included in this bank code which combines text of multiple rows at each change in date. I need your expertise to edit both the codes wherever required and get the result as shown in the expected result sheet. I request you to please add a comment at line of code so that I can create multiple apps depending on each bank.
The sheet Bank is the original form as and when the data is received. I need the code for this sheet and get the result as in sheet Expected Result.
P.S: The code in the visual basic is only for reference purpose as it will help to solve more than 50% of the work. To test the code Bank Clean Data in the workbook you have to unhide the bank and name it as Bank.Customize New Bank.xlsm
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I am not able to edit the code accordingly. AS the code was shared without comments, I don’t have any idea what some of the lines in the code actually do.

Here is the code commented for you.

VBA Code:
Option Explicit

Sub Bank_CleanData()
'updated by Johnnyl 13-05-2022
'shared by CheeseSandwich
    Dim ar
    Dim i                       As Long
    Dim n                       As Long
    Dim lr                      As Long
    Dim BranchName              As String, ChequeNo As String
    Dim sname                   As String
    Dim arr(1 To 10000, 1 To 9)
    Dim hdr                     As Variant
    Dim ws1                     As Worksheet, ws2   As Worksheet
'
    Application.ScreenUpdating = False                                          ' Turn ScreenUpdating off
'
    hdr = Array("Line", "Txn Date", "Month", "Voucher Type", "Dr Amount", _
            "Cr Amount", "Balance", "Check", "Narration")                       ' Array of headers to write to the created sheet
'
    Set ws1 = Worksheets("Bank")                                                ' <-- Set this to the sheet to use for the input data
'
    ar = ws1.[A1].CurrentRegion                                                 ' Save data from input sheet to array called 'ar'
    lr = UBound(ar, 1)                                                          ' lr = last row of array called 'ar'
    sname = Replace(CStr(ar(lr, 2)) & " to " & CStr(ar(2, 2)), "/", "-")        ' Get Date range for the sheet ... This wil be used for the added sheet name
'
    For i = 2 To UBound(ar, 1)                                                  ' Loop through the rows of data
        BranchName = "": ChequeNo = ""                                          '   Clear the BranchName & ChequeNo
        n = i - 1                                                               '   Set 'n' = 1 less than the row # (Line)
'
        arr(n, 1) = n                                                           '   Save the Line # to Column 1 of array called 'arr'
        arr(n, 2) = ar(i, 2)                                                    '   Save 'Txn Date' to Column 2 of array called 'arr'
        arr(n, 3) = ar(i, 2)                                                    '   Save 'Txn Date' to Column 3 of array called 'arr'
        arr(n, 5) = ar(i, 6)                                                    '   Save 'Dr Amount' to Column 5 of array called 'arr'
'
        If arr(n, 5) <> 0 Then arr(n, 4) = "Payment"                            '   If 'Dr Amount' <> 0 then put "Payment" into Column 4 array called 'arr'
        arr(n, 6) = ar(i, 7)                                                    '   Save 'Cr Amount' to Column 6 of array called 'arr'
        If arr(n, 6) <> 0 Then arr(n, 4) = "Receipt"                            '   If 'Cr Amount' <> 0 then put "Receipt" into Column 4 array called 'arr'
        arr(n, 7) = Replace(ar(i, 8), Chr(10), "")                              '   Save 'Balance' into Column 7 of array called 'arr'
        arr(n, 8) = CDbl(Left$(arr(n, 7), Len(arr(n, 7)) - 4))                  '   Save 'Balance' into column 8 of array called 'arr' without the ending
        If ar(i, 4) <> "-" Then BranchName = " Branch Name " & ar(i, 4)         '   If 'Branch Name' <> "-" then save Branch Name to BranchName
        If ar(i, 5) <> "" Then ChequeNo = " Cheque no. " & ar(i, 5)             '   If 'Cheque No.' is not blank then save Cheque No. to ChequeNo
        arr(n, 9) = ar(i, 3) & " Txn no. " & ar(i, 1) & BranchName & ChequeNo   '   Save 'Description & Txn No. & BranchName & ChequeNo to Column 9 array called 'arr'
    Next i                                                                      ' Loop back
'
    Sheets.Add(After:=Sheets("Bank")).Name = sname                              ' Add new sheet after the sheet used for the input
    Set ws2 = Worksheets(sname)                                                 ' Set the new sheet to 'ws2'

    With ws2
        lr = UBound(ar, 1)                                                      '   lr = last row of array called 'ar' ... this has already been set ;)
        .[A1].Resize(, 9) = hdr                                                 '   Write the array of headers to first row of new sheet
        .[A2].Resize(UBound(ar, 1) - 1, 9) = arr                                '   Display array called 'arr' to new sheet starting on row 2
'
        .Columns("I:I").WrapText = False                                        '   Set WrapText for column I of new sheet to False
        .Columns("C:C").NumberFormat = "mmm-yyyy"                               '   Set Date format of new sheet Column C to "mmm-yyyy"
        .Columns("E:F").NumberFormat = "0.00"                                   '   Set NumberFormat of Columns E & F on the new sheet to 2 decimal places
'
        .Range("A1:I" & lr).Sort Key1:=.Range("A1"), Order1:=xlDescending       '   Sort the new sheet according to the Column A, Highest to lowest
'
        For i = 2 To lr                                                         '   Loop through the rows of data on the new sheet
            .Cells(n, 8) = .Cells(i, 8) + .Cells(n, 6).Value - .Cells(n, 5).Value   '       Correct the amounts in the 'Check' column of the new sheet
            n = i + 1
        Next i                                                                  '   Loop back
        With .Columns("A:I")                                                    '   Format Columns A:I on the new sheet
            .Font.Name = "Calibri"
            .Font.Size = 11
            .AutoFit
        End With
        .Columns("I:I").Replace What:="" & Chr(10) & "", Replacement:=" ", _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False                       '   Remove any Line Feeds in Column I of the new sheet
'
        .Range("H3:H" & lr).Formula = "=IF(RIGHT(RC[-1],3)=""Dr.""," & _
                "R[-1]C-RC[-2]+RC[-3],R[-1]C+RC[-2]-RC[-3])"                    '   Apply formulas to Column H of new sheet ... =IF(RIGHT(G3,3)="Dr.",H2-F3+E3,H2+F3-E3)
        .Range("H3:H" & lr).Value = .Range("H3:H" & lr).Value                   '   Remove formulas from Column H in the new sheet leaving just the values
'
        Application.ScreenUpdating = True                                       '   Turn ScreenUpdating back on
        If Replace(Left(.Range("G" & lr).Value, InStr(.Range("G" & lr).Value, _
                ".") + 2), ",", "") = Trim(Int(.Range("H" & lr).Value _
                * 100) / 100) Then                                              '   If last row of Column G = last row of Column H then ...
            MsgBox "Data cleaned & Matched Sccessfully"                         '       Display Matched message
        Else                                                                    '   Else ...
            MsgBox "Mismatched. Check if any row is missed to enter"            '       Display Mismatched message
        End If
    End With
End Sub

See what you can do with that.
 
Upvote 0
Thanks JohnnyL. Let me check and try it out.
 
Upvote 0
My edited code is inserting a blank sheet but is stopped at this line as run time error 1004
Rich (BB code):
    Sheets.Add(After:=Sheets("Bank")).Name = sname                              ' Add new sheet after the sheet used for the input
 
Upvote 0
There are many unwanted lines in the code which I have commented them out. Like there is no Dr or Cr in the amount column, there is no txn no., there is no branch name, and in the new ws2 there is only one date column. I have commented all those lines.
 
Upvote 0
what is the error 1004 code message?
Untitled.png
 
Upvote 0
What does sname show when you hover your mouse over it after you click on 'Debug' and the line is yellow
 
Upvote 0
I am guessing that you probably commented the line out up above that line that sets sname value
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top