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

 

[johnnyL]

I expect you to comment your code also. That way we can see what madness you were thinking when you put the lines of code in or change the lines of code. It is not fair just to peek into my mind's crazy thinkings without offering your own.

 

[RAJESH1960]

I want to finish with the code first since I am editing each line now and then, then, when finally it works, I wanted to comment them.
Okay here is the closing balance column.

 

[RAJESH1960]

I think if I add the lines of the Join text code in the beginning of the code it would be easier to get the expected result.

 

[johnnyL]

I think if I add the lines of the Join text code in the beginning of the code it would be easier to get the expected result.

The following code will create the new sheet, copy the 'Bank' sheet to it, & then do any text joining, followed by deleting all of the blank rows that remain after that.

From that point you can run code on the new sheet instead of the original 'Bank' sheet:

Option Explicit

Sub ConcatTestV2()                                                                  ' For Bank statements
'
'solved by JohnnyL 24-06-2022
'works in the sheet which is open

    Dim ArrayRow                As Long, OutputRow          As Long
    Dim BlankRows               As Long, CurrentRow         As Long
    Dim StartRow                As Long
    Dim ConCatColumnLetter      As String
    Dim DateColumnLetter        As String
    Dim sname                   As String
    Dim InputArray              As Variant, OutputArray()   As Variant
    Dim ws1                     As Worksheet, ws2           As Worksheet
'
    Set ws1 = Worksheets("New Bank")                                                ' <-- Set this to the sheet to use for the input data
'
    ConCatColumnLetter = "B"                                                        ' <--- Set this to column that needs Concat function
    DateColumnLetter = "A"                                                          ' <--- Set this to column of Dates
    sname = "BankData"                                                              ' <--- Set this to the new sheet name
    StartRow = 2                                                                    ' <--- Set this to the starting row of data
'
    Sheets.Add(After:=ws1).Name = sname                                             ' Add new sheet after the sheet used for the input
    Set ws2 = Worksheets(sname)                                                     ' Set ws2 to the sheet to use for the output data
'
    ws1.UsedRange.Copy ws2.Range("A1")                                              ' Copy Source sheet to new sheet
'
    InputArray = ws2.Range(DateColumnLetter & StartRow & ":" & ConCatColumnLetter & _
            ws2.Range(ConCatColumnLetter & Rows.Count).End(xlUp).Row).Value2        ' Save data from sheet into InputArray
    ReDim OutputArray(1 To UBound(InputArray, 1), 1 To UBound(InputArray, 2))       ' Set OutputArray to same # of rows & columns as the InputArray
'
    BlankRows = 0                                                                   ' Initialize BlankRows
    OutputRow = 0                                                                   ' Initialize OutputRow
'
    For ArrayRow = 1 To UBound(InputArray, 1)                                       ' Loop through the rows of the InputArray
        If InputArray(ArrayRow, 1) <> vbNullString Then                             '   If Date is not blank then ...
            OutputRow = OutputRow + 1                                               '       Increment OutputRow
'
            CurrentRow = OutputRow + BlankRows                                      '       Get total of OutputRow + BlankRows and save to CurrentRow
            OutputArray(CurrentRow, 1) = InputArray(ArrayRow, 2)                    '       Save Concat word to OutputArray(CurrentRow, 1)
        Else                                                                        '   Else ...
            BlankRows = BlankRows + 1                                               '       Increment BlankRows
            OutputArray(CurrentRow, 1) = OutputArray(CurrentRow, 1) & _
                    " " & InputArray(ArrayRow, 2)                                   '       Append a space & next Concat word to OutputArray(CurrentRow, 1)
        End If
    Next                                                                            ' Loop back
'
    With ws2
        .Range(ConCatColumnLetter & StartRow & ":" & ConCatColumnLetter & _
                .Range(ConCatColumnLetter & _
                .Rows.Count).End(xlUp).Row).Value2 = OutputArray                    '   Write Concat column back to new sheet
        .UsedRange.WrapText = False                                                 '   Set WrapText for new sheet to False
        .UsedRange.EntireColumn.AutoFit                                             '   Autofit all used columns in the new sheet
        .UsedRange.EntireRow.AutoFit                                                '   autofit all rows in the new sheet
'
        On Error Resume Next                                                        '   Ignore error in next line if no blank rows were found to delete
        .Columns("A").SpecialCells(xlBlanks).EntireRow.Delete                       '
    End With
End Sub

 

[RAJESH1960]

Hi JohnnyL. So far, I have not been able to do the below steps correctly. I have renamed the sheet created by join text as Bank. I tried placing call join text in the beginning of the code so that I have to run the code once only but failed in that too. Please help me to complete this by correcting the code as I have reached the stage of etc (End of thinking capacity).
1. Get voucher type right.
2. Combine Narration with Chq./Ref.No. if Chq./Ref.No.<> 0
3. Get the value of the cell J2 which can be either F2 or G2. So, J2=F2+G2 - (Closing balance). Align columns F,G,I AND J to left.
The above may correct to get the optional message box right.

Pending issues.xlsm

 

[RAJESH1960]

Phew!!! I was able to solve some of the issues. Got the voucher Type right and Narration partially got it right. I am getting the Narration combined with Chq./Ref.No. but if the Chq./Ref.No. is 0, then also it is joining both.

Pending issues.xlsm

 

[RAJESH1960]

    ar = ws1.[A1].CurrentRegion                                                 ' Save data from input sheet to array called 'ar'

Not sure but, Should I add -1 in this line to avoid copying the last row from NewBank to Bank.? Sorry, I have to edit it in the Join text code.

 

[johnnyL]

You can't put a '-1' in that line of code.

 

[RAJESH1960]

In the Join text code, where do I edit to avoid getting the last row in the NewBank sheet.? Checked and tired most of the lines at a time, but failed to remove that line.

 

[RAJESH1960]

How about in this line....

    InputArray = ws2.Range(DateColumnLetter & StartRow & ":" & ConCatColumnLetter & _
            ws2.Range(ConCatColumnLetter & Rows.Count).End(xlUp).Row).Value2        ' Save data from sheet into InputArray