Clean & Customize xlsx sheet with a code

Hello code experts
With the help of a macro I recorded, I have been able to get the expected result correctly. The problem is that this will work only with this data only. I am no expert in writing hard core codes. If the ranges are corrected with a few other problems which are commented in the code, I think there would be no problem to clean and customize every different original bank data received in this format. So, Please, I would really appreciate your help in correcting the code with your expertise. Thank you in advance.
Please note: If you play the code from the visual basic window, the result is displayed in a newly created sheet. If the sheet name can be renamed as the first date and the last date from the date column it would be really cool. In this case, it is 01-03-2022 to 31-03-2022.

This is just a sample data. In some cases there are 3000-5000 rows. So, if the range of the rows in the code is set to unlimited or till the last row, it would really be great.

Try

Please Login or Register  to view this content.

John. I am getting an error = variable not defined/ How do I define hdr - variable not defined.? trying variant....

Please Login or Register  to view this content.

You're very welcome. Thank you for the feedback and rep.

John Topley. I failed to notice that you have missed one step. In the H column you have just removed the format of Cr of Balance column and pasted it.
After sorting the line from larges to smallest, i require to do that only for the first cell i.e., H2, In H3 I needed to apply a formula and drag it down and then check whether the Balance column last amount and Check column last amount are a match.
This code is my recorded macro.

HTML Code: 

This is to check, while pasting the data of the bank I haven't missed any rows. Please help me to edit the code accordingly. As mentioned in the comments of my code, please get the message box if matched - "Matched Successfully" if it is matched. else "Mismatched" if not matched.

HTML Code: 

This is the code used in earlier project to get the msgbox. But the range is different in this case.

HTML Code: 

This will complete the project hopefully.

Please Login or Register  to view this content.

Hello guys
This is a code written and shared by John Topley. It is working in some cases and in some cases it is not. I am not able to figure out the issue for the error. Please help me to correct this error
The name of the Sheet should be Bank to test the code. I have placed 2 sheets of which one is producing the result correctly and the other is not.
One more thing, the msg box is showing mismatch even if it is matched which may be due to the format of the cell in column H. If you are able to edit the range of the selected cell to match without the format in the msgbox code it will be just great.
Thank you in advance experts.
Error at

HTML Code: 

It is because you are calling the 53rd instance in array that only contains 51 items. There are only 51 lines of data on the 'Bank' tab so therefore there are 51 parts to the array 'ar'

If you change this line:

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

Then you will be able to call 'ar(53, 1)'

I started writing answer in other thread but it was closed by one of moderators, so I'm copying my answer here:

one line above the error-causing line:

Please Login or Register  to view this content.

you have filled ar array with values from A1:H51
obviusly, this array has only 51 rows, so

Please Login or Register  to view this content.

returns an error.

Continuation thread has been moved here - please don't open duplicate threads on the same issue. Thanks.

Got it. Kaper. Can you please tell me how to edit that line so that I can work with the same code with different (unlimited rows) ranges.
I can't even find this line in the code where it shows the ar array is filled with values from A1:H51.

Post 20 might have a clue...

I can't even find this line in the code where it shows the ar array is filled with values from A1:H51.

Please Login or Register  to view this content.

but I am getting the same error

What error?

Originally Posted by RAJESH SHAH

Ok. So, the code will select the region in the Bank sheet whatever the number of rows. Right?

Yes...provide there are no blank rows between...

Run time error 9 at Debug.Print ar(53, 1)

You've already been told above why that happens...Your Arr has not got that many rows stored...

See attached...

Please Login or Register  to view this content.

CurrentRegion returns a Range object that represents a set of contiguous data

Your code is fixed to run on the 'Bank' tab. The bank tab has 51 rows of data. That means there will be 51 items in the array.

Are you trying to run it on a different sheet?

Post 20 explains how to run it on the Bank2 tab where there will be no error on that line of code as there are more than 51 rows of data.

Look at post 34 to understand...

I am sharing this workbook with only one sheet and please tell me why it is running into an error and what is the possible solution.

You have already been told on multiple occasions above...
It errors as their is only 51 rows in that worksheet

Please Login or Register  to view this content.

proves this...So this line below will error as you are trying to get a value from row 53 which does not exists...

Please Login or Register  to view this content.

No idea why each ws in sheets & next ws

Was trying to show you in layman's terms how the storing of data into an array works...

Please tell me how to solve this.

Remove this line...As there is no row 53

Please Login or Register  to view this content.

The same code is working with a different data. Please check this too.

Once again...It is working because this data has more than 53 rows...

If i comment that line out, i am getting another error run time error Type mismatch at .Cells(n, 8) = .Cells(i, 8) + .Cells(n, 6) - Cells(n, 5)

That's because that code is trying to do a calculation on strings

I will not intervene as I do not like to work on Other Members' Code...

I have cleaned up the code somewhat in the below, see if it gets you any closer.

Please Login or Register  to view this content.

It is because on one set of data the numbers end with 'vbnewline & #Dr' and on the other set you have the numbers ending with 'space Cr.'

Try the below:

Please Login or Register  to view this content.

This code is running in both the workbooks. I will try it in the original data base now. Hope I don't face any problems.

Add them back in then - I am helping you to get your code right.

I left that bit out as the way i see it this:

Please Login or Register  to view this content.

Is doing the same as:

Please Login or Register  to view this content.

The difference being that one creates the sum and writes the result into the cell and the other overwrites the same cell with a formula that is doing the same thing.

Phew!! Finally solved with the required changes with the help of JohnnyL. Thanks to Cheese Sandwich and Sintek also for there support.

I am not able to mark this post solved. Will the admin please tell me how to or mark the post as solved.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.