Code to Customize Bank statement

[RAJESH1960]

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

 

[RAJESH1960]

No I haven't commented out that line

 

[johnnyL]

Oh, you have sname pointed to wrong value.

 

[johnnyL]

Change that sname line to whatever you want to call the new sheet.
Ex:
sname = "NameOfNewSheet"

Your commenting out of lines has altered the values that use to work for that line.

 

[RAJESH1960]

How do I define the variable for the new sheet name. Should I define it as
Dim Bank_Customised as worksheet

 

[RAJESH1960]

I defined it as Dim BankData as string and it again showed me the same error at the same line as before.

Sheets.Add(After:=Sheets("Bank")).Name = sname

 

[johnnyL]

It is already defined at the top 'Dim sname as string'

If you want to change 'sname' make sure you change it in all locations it appears in the code

 

[RAJESH1960]

It is already defined at the top 'Dim sname as string'

If you want to change 'sname' make sure you change it in all locations it appears in the code

It was pointing me to the new sheet name and showing an error as variable not defined.

 

[RAJESH1960]

Option Explicit

Sub Bank_CleanData()
'updated & commented by Johnnyl 13-05-2022
'shared by CheeseSandwich
'trying to edit myself 26-06-2022

    Dim ar
    Dim i                       As Long
    Dim n                       As Long
    Dim lr                      As Long
    Dim ChqRefNo                As String
    Dim ValueDt                 As String
    Dim BankData                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", "Voucher Type", "Voucher No.", "Tally Ledger Name", _
            "Tally Ledger Name", "Withdrawal Amt.", "Deposit Amt.", "Narration", "Check")                      ' 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 = BankData                                                          ' 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
        ChqRefNo = "": ValueDt = ""                                          '   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, 5)                                                    '   Save 'Dr Amount' to Column 5 of array called 'arr'
'
        If arr(n, 5) <> 0 Then arr(n, 5) = "Payment"                            '   If 'Dr Amount' <> 0 then put "Payment" into Column 4 array called 'arr'
        arr(n, 6) = ar(i, 6)                                                    '   Save 'Cr Amount' to Column 6 of array called 'arr'
        If arr(n, 6) <> 0 Then arr(n, 6) = "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, 9), Len(arr(n, 9)) - 4))                  '   Save 'Balance' into column 8 of array called 'arr' without the ending
        If ar(i, 4) <> "-" Then ChqRefNo = " Chq./Ref.No. " & 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, 2) & "  Chq./Ref.No. "                            '   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("F:G").NumberFormat = "0.00"                                   '   Set NumberFormat of Columns E & F on the new sheet to 2 decimal places
'
        .Range("A1:J" & lr).Sort Key1:=.Range("A1"), Order1:=xlAscending       '   Sort the new sheet according to the Column A, lowest to Highest
'
        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:J")                                                    '   Format Columns A:I on the new sheet
            .Font.Name = "Calibri"
            .Font.Size = 11
            .AutoFit
        End With
        .Columns("J:J").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("J3:J" & lr).Formula = "=IF(RIGHT(RC[-1],4)=""""," & _
                "R[-1]C-RC[-3]+RC[-4],R[-1]C+RC[-3]-RC[-4])"                    '   Apply formulas to Column J of new sheet ... =J2+G3-F3
        .Range("J3:J" & lr).Value = .Range("J3:J" & 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("I" & lr).Value, InStr(.Range("I" & lr).Value, _
                ".") + 2), ",", "") = Trim(Int(.Range("J" & 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

This is all the editing I could think of.

 

[RAJESH1960]

Sheets.Add(After:=Sheets("Bank")).Name = sname

It runs till here and displays the error after inserting the new sheet which is blank and not renamed

 

[johnnyL]

You are making this harder than it has to be.

Change that Dim Bankdata back to Dim sname
Put quotes around BankData in the line that you have as sname = BankData .... it should be sname = "BankData"