QuestionQuestion

Transcribed TextTranscribed Text

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

Solution PreviewSolution Preview

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.

Sub find_customers()

Dim amount, total_amount As Currency
Dim size, r_size, i, pos, curr As Integer

size = Cells(Rows.Count, "A").End(xlUp).Row

ActiveWorkbook.Worksheets("Report").Select
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
Loop

ActiveWorkbook.Worksheets("Report").Select
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...

By purchasing this solution you'll be able to access the following files:
Solution.xlsm.

$25.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Computer Science - Other Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats