Printable View
I'm working on an Excel spreadsheet to track sales data. I have columns for product name, quantity sold, and unit price. I want to create a macro using VBA code that will:
Calculate the total sales for each product by multiplying quantity by unit price.
Identify the top 3 best-selling products based on total sales.
Can you help me write the code to achieve this functionality?
What makes you think you cant do this by formula?
Would also be a good idea to share a spreadsheet with some dummy data, you could lay the data out as you have it currently. Members here will be more likely to assist if they don't have to recreate your scenario.
As Aussie has pointed out above, I am sure this could be done with formula, this would allow for your stats to update with future data being added.
You could do this with formulas as Aussiebear alluded to.
This just has price * Sold, then uses SortBy function to sort the total descending and finally a section that just uses cell linking to the top 3 rows of the sorted section.
You can do this with VBA if you want to, if there is more you would like to control or want to switch things up later.
Forcing Excel to perform as a database could be wrong path. Very large workbooks can become sluggish to the point of non-functional. How much data do you expect to collect? Are you documenting discrete transactions or just aggregated values and you will be changing the values periodically? "Tracking" implies discrete transactions but I suspect not since you don't mention attributes of OrderNum, OrderDate, CustomerID.
If you stick with Excel, consider learning Power Query.
Chuck it into a pivot table. No code, no formulas, aside from a calculated column.
Like Bob says, a pivot table would be the best / easiest / no VBA required way to go
It would give you the most flexibility for reporting and analysis
I would think that there's more information you'd want to trck, e.g. sales date, customer, etc.
Attachment 31479
I think the OP has lost interest in this thread....