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?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Chuck it into a pivot table. No code, no formulas, aside from a calculated column.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
Capture.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
I think the OP has lost interest in this thread....
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Reply With Quote
