Help with VBA homework!!
Write a macro that processes the following sales data (your final code should work with data that have any number of products, different number of price breaks, different quantities for a price break, different of extra discount, different amount for a total discount and a list with more or fewer clients It can be safely assumed that the data in the "Products" worksheet has the word "Product" in an anchor cell (e.g., cell A4) and the "Extra Discount" and "lf more than" values are two columns to the right from the last column with price breaks. Similarly, the "Total Ordered", "Total Amount and "Total Amount (with extra discount)" columns are after skipping one column (as shown in the example below). In the worksheet named "Products" the following example data are given Unit Price for different quantities ordered unit price Minimum Order Quantity to get Tota Amount with extra Tota Tota 4 Product Ordered Amount discount 100 500 1000 Extra Discount $10.25 $10.20 $9.51 5 Gadget 9.04 5.00% $12.40 $11.72 $11.42 $11.14 6 Gadget2 If more than $9.50 $8.45 7 Gadget 9.00 8.92 25,000.00 Thus, for example if a customer orders between 100 and 499 units of Gadget1 the unit price is $10.20 for each unit, not only for the units in excess of 100. Each customer can order different quantities of each of the gadgets. If the total dollar amount ordered by a customer exceeds $25,000.00 (number specified in cell G7 for this example three rows down from the anchor cell i.e., cell "A4" in this example) and two columns to the right of the last column with price breaks) then the customer receives an extra discount of 5% (number in cell G5 one row down from the anchor cell and two columns to the right of the last column with price breaks) of the total purchase. Note that headers in this "Products" worksheet are pre-specified The worksheet named "Clients" has a list of 10 clients (it can have more or fewer) with different quantities of products ordered. The program will populate the columns to the right of the last column with data (including headers). For example, the "Amount Before Extra Discount paid by the last client for Gadget1 is $9,311.20 (-1030*$9.04); that is, the amount in cell F14 (in this example) of the clients worksheet would be $9,311.20. The "Total Amount" in column J in the sum of the "Amount Before Extra Discount" over all products and depends on the quantities ordered as indicated in the worksheet "Products." If, however, the "Total Amount" of purchase exceeds the "if more than" in the worksheet "Products," ($25,000.00 indicated in cell G7 in this example) then the percent "extra discount" specified in the worksheet "Products" (cell G5 in this example) applies in addition to the discounts obtained by the quantity ordered of each gadget i.e., in this example the 5% discount applies to each unit cost of the gadget bought by the customer). Note that the headers in this worksheet are pre-specified for the Clients, and the Write a macro that processes the following sales data (your final code should work with data that have any number of products, different number of price breaks, different quantities for a price break, different of extra discount, different amount for a total discount and a list with more or fewer clients It can be safely assumed that the data in the "Products" worksheet has the word "Product" in an anchor cell (e.g., cell A4) and the "Extra Discount" and "lf more than" values are two columns to the right from the last column with price breaks. Similarly, the "Total Ordered", "Total Amount and "Total Amount (with extra discount)" columns are after skipping one column (as shown in the example below). In the worksheet named "Products" the following example data are given Unit Price for different quantities ordered unit price Minimum Order Quantity to get Tota Amount with extra Tota Tota 4 Product Ordered Amount discount 100 500 1000 Extra Discount $10.25 $10.20 $9.51 5 Gadget 9.04 5.00% $12.40 $11.72 $11.42 $11.14 6 Gadget2 If more than $9.50 $8.45 7 Gadget 9.00 8.92 25,000.00 Thus, for example if a customer orders between 100 and 499 units of Gadget1 the unit price is $10.20 for each unit, not only for the units in excess of 100. Each customer can order different quantities of each of the gadgets. If the total dollar amount ordered by a customer exceeds $25,000.00 (number specified in cell G7 for this example three rows down from the anchor cell i.e., cell "A4" in this example) and two columns to the right of the last column with price breaks) then the customer receives an extra discount of 5% (number in cell G5 one row down from the anchor cell and two columns to the right of the last column with price breaks) of the total purchase. Note that headers in this "Products" worksheet are pre-specified The worksheet named "Clients" has a list of 10 clients (it can have more or fewer) with different quantities of products ordered. The program will populate the columns to the right of the last column with data (including headers). For example, the "Amount Before Extra Discount paid by the last client for Gadget1 is $9,311.20 (-1030*$9.04); that is, the amount in cell F14 (in this example) of the clients worksheet would be $9,311.20. The "Total Amount" in column J in the sum of the "Amount Before Extra Discount" over all products and depends on the quantities ordered as indicated in the worksheet "Products." If, however, the "Total Amount" of purchase exceeds the "if more than" in the worksheet "Products," ($25,000.00 indicated in cell G7 in this example) then the percent "extra discount" specified in the worksheet "Products" (cell G5 in this example) applies in addition to the discounts obtained by the quantity ordered of each gadget i.e., in this example the 5% discount applies to each unit cost of the gadget bought by the customer). Note that the headers in this worksheet are pre-specified for the Clients, and the
