Replacing Formulas with Code

Hi,

I have a problem with a report I am converting from PDF to Excel.

The problem is, it doesnt recognise certain data correctly and as a result columns are merged which distorts the data.

In the attached I have shown the orignal report in columns A:H along with what I need in J:Q. I have used formulas for this but ideally would I would like is a code that would work and do this for me, can anyone help please?

One thing to note is that I wouldnt want the data in column J:Q - I would like it to replace the orignal data table A:H

Please note in my data i have manually inputted the sub totals manually - ideally I would like the code to do this. A similar code was supplied on a different post for this here (I just need to work out how it works)

HTML Code: 

Any suggestions are very much welcomed.

Hi John

Thanks for looking into this for me.

The code doesnt work from row 20 down. What I need is the values shifted accross one column to the right and then in column B for it to strip out the customer name excluding the first two digits.

Also, could it be applied to the orignal sheet (active sheet)?

Regarding the sub totals:
What I would like a code to do is look in column A for the words “sub total” and if found I would like it to sum the rows above until if finds the previous subtotal. The columns range is C:H

Then if it finds “Net Settlement Total” I would like it to sum all the sub totals just generated (above)

Does that help?

Hi John

This was missed on the sample upload. I have now included it along with the desired results

Paul

Hi,

It should be run through columns A to H (the new part is only for reference of what I am trying to achieve in cells A:H).

Regarding the same sheet I see some codes take the oringal sheet and then make the amendments to the new sheet however I would like it applied to the original sheet.

Hi Jaslake

I have only just seen your reply, normally I rely on a notifcation which I didnt recieve.

This is great!! So far so good but will test fully

Hi,

It does indeed work great for the dummy data but not for my actual template but I think it will work with a couple of tweeks.

Is there any way after the current code is run it could delete the rows (after row 20) if:

• Row is blank
• Row contains (exactly in column A) "App024", ,"Retail, "Excel VAT", "Business", "Inter", "Period", "Code", "Page", "End"

I think the code for the above is similar to this below (cut from a previous code) :

Please Login or Register  to view this content.

Then I would need the header rows inputted and formatted:

Please Login or Register  to view this content.

Can you help in combining all codes please?