Name your spreadsheet with your network login followed by _a02.xlsm. For example, if your
network login was barn4520, then your assignment file should be named barn4520_a02.xlsm.
The file CustomerOrders.xlsx shows orders by date for a company’s customers on the Data worksheet.
Many customers have ordered more than once, so they have multiple entries in the list. Write a sub that
asks the user for a total (for example, $3000), finds the total amount spent by each customer on the list
and reports those whose total is more than the amount provided by the user on a new worksheet called
Report. Have the program ask the user for a total until the user enters a valid value.
As part of your sub, sort the list on the Report worksheet in ascending order by total amount spent.
(Hint: The orders in the Data worksheet are currently sorted by date. It might be helpful to use VBA to
sort them by Customer ID (use built-in sort don’t code sort yourself). Then at the end of the sub, restore
the list to its original condition by sorting on Date.)
Make sure your code checks for the Report worksheet before it does any new calculations. Any previous
existing Report worksheet should be deleted before a new one is created.
Create your Results worksheet so it looks something like this before your data gets filled in:
Notes and Tips:
• Be sure to include basic data validation/error checking to avoid data type mismatches and other
errors. Have the program ask the user for a total until the user enters a valid value.
• Make a button on the Data worksheet to run your subroutines
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction
of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice.
Unethical use is strictly forbidden.
Dim amount, total_amount As Currency
Dim size, r_size, i, pos, curr As Integer
size = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & size).Clear
Range("B1:A" & size).Clear
Range("C1:A" & size).Clear
Do While amount <= 0 Or IsNumeric(amount) = False
amount = InputBox("Enter the amount: ")
If IsNumeric(amount) And amount > 0 Then Exit Do
Range("A1") = "Customers who spent more than $" & amount
Range("A3") = "Customer ID"
Range("B3") = "Total amount spent"
Range("A3").HorizontalAlignment = xlCenter
Range("B3").HorizontalAlignment = xlCenter...