vba macros to apply formula for dynamic Auto Adjusting range references

Hello Again,

I am looking for a VBA script where I can place cell reference for dynamic range.

I am placing a formula on a cell value but the problem is total amount if changing based on months and count of rows.

For example:
1 scenario the total records is having 12 rows of data and grand total at the last for Provider 10 – 2017 and the formula will be as below:

Please Login or Register  to view this content.

But the problem is Column E value (Final Pay is changing), Column 8 value changing total amount.

So I need to have a solution the values will be read where the statement is ending.

Sample file is attached for your reference.

Can you possibly change your data into tables? You can then reference the table column totals in the formula's like such

Please Login or Register  to view this content.

I changed the 3rd statement in the sample into a table, and you can see the formula there.

Thanks for your response.

The above attached is part of vba macros which is generating from huge data based if specific provider per year.

Every statement is prepared based in month which is keep changing the range.

So this is the reasion i have requested for vba.

Can you provide an updated sample of both a before and after? And possibly share the VBA code that is currently being used?

I see your formula that you referenced are used in columns 14 and 15 and basic sum formula in column 16. Are you adding those columns and formulas after your macro runs or are they part of the macro?

and the same included 3 statements, but I assume there could be more going down the worksheet?

Dear Ptmuldoon,

As requested please find the attachment.

See the highlighted yellow cells which having formula but keep change based on number of rows.

I can probably improve on this some more but wanted to get back you the progress.

I'm not the best coder here, so others can probably improve on this alot.

Run the below on your statement and it should format and compute your formula's for you.

Let me know if it works and I can try and clean up the code some.

Please Login or Register  to view this content.

Thanks for your help it is valuable for me.

I will test it and get back to you if required.

Thanks a lot once again.

Dear Ptmuldoon,

It is not working as the number of rows are dynamic.

Some records are having 2 or 4 like the sample attachment but some of them are having 1 or 7 or 3 or 12. Meaning it is not fix.

The data which I have attached #5 is the sample only.

Yes, I used your sample file and it is working for me as I understood your issue.

The code is adding total for each Provider Name. And then creating the formulas in columns N and O, and references those total rows.

Here is your file back that I worked with. Run the code on the Statement or test worksheet.

I could add additional code to copy the worksheet first, but wanted to make sure its working for you as expected.

Thanks Ptmuldoon,

It is working as I want.

I will mark topic as closed.