I'm trying to calculate the weighted average with some criteria (period, store and product) of the Data file and for each average of each product of each store in a certain period, I wanted to paste it into another Workbook (Report) in the worksheets of the respective products.
The problem is that the code I am writing, is not calculating based on these criteria (returns 0.00), if someone can tell me where I am wrong (I am a beginner in VBA), I am grateful.
I already used PivotTables and today I do this calculation through formulas (sumproduct), both take a long time to calculate, because the data file is very large (from 400,000 to 600,000 lines), the computer screen arrives To be dimmed.
The weighted average for product "101" of store "A" in Jan / 17 is:
(each quantity * each unit price) / quantity sum
((13 * 6) + (12 * 5)) / (13 + 12) which will be equal to 5.52
I'm trying to do for each product per store per month / year.
And do for each product with all the stores together per month / year.
My intention is to automate in VBA because the time gain would be better. And since I'm starting in VBA, it would be a learning experience for me.
Sorry for my english, I'm using google translator.
Follow the files for better understanding.
Thank you for your attention.