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]

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.

Did you miss that post?

 

[RAJESH1960]

Oops... I missed that. Now the code has run 5 more lines, created the bank data sheet and some of the data.

 

[RAJESH1960]

I got all the headers all wrong and not in the expected order. No date column, 2 narrations columns, cheq.Ref No. column which was not needed.....

 

[johnnyL]

Headers in wrong order? Are you sure the data is not in the wrong order? It is kind of difficult to mess up the order of the headers.

 

[RAJESH1960]

ROFL.

 

[johnnyL]

The following is the original Header array:

    hdr = Array("Line", "Txn Date", "Month", "Voucher Type", "Dr Amount", _
            "Cr Amount", "Balance", "Check", "Narration")                       ' Array of headers to write to the created sheet

And the following is the line that prints it to the new sheet:

        .[A1].Resize(, 9) = hdr                                                 '   Write the array of headers to first row of new sheet

Like I said, kind of hard to mess it up.

What do you have for hdr line of code now?

 

[RAJESH1960]

I missed to write the "date".
Give me some time. By tomorrow I will finish if possible to get at least the headings and data in the rows right.

 

[johnnyL]

Another Tip ... When you are commenting out a line, put the ' at the beginning of the line, not right next to words in the line that you are commenting out. It makes it easier to spot the lines that are commented out if you have the apostrophe at the beginning of the line.

 

[RAJESH1960]

Okies.

 

[RAJESH1960]

Can you please tell me these lines which is what

For i = 2 To UBound(ar, 1) ' which data is it referring to Bank or new sheet.
What does n stand for exactly n = i - 1
Then again arr(n, 1) = n,,,,??