Write a macro that processes the following sales data (your final code should wo

Question

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 “If 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:

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 theproduct names but the headers for all other columns have to be appropriately generated and

placed through VBA. That is, the cells populated by the program in all the worksheets should be

cleaned out every time the program is run and before calculating the new values.

Using the data provided, the macro will calculate the “Amount Before Extra Discount” for each

product, “Total Amount” before extra discount, “Amount After Discount (if any)” for each product,

and “After Discount Total Amount” for each client in the worksheet “Clients”. In the worksheet

“Products” report the number for each product under the “Total Ordered” header, the “Total

Amount” before the extra discount for each product and the “Total Amount (with extra discount)”

for each product.

(Hint: Initially use cell “A4” as anchor cell in the products worksheet and also in the Clients

worksheet. Define range variables for the products and the price breaks and assign the number

of products and the number of price breaks to variables. Assign to variables the extra discount

percentage and the amount required for the extra discount. You may need to use nested For

Next loops to loop through Clients, then through Gadgets and then through Price Breaks. The

loop through Price Breaks may be easier to do going from the highest to the lowest order

quantity for the price break.)

Your code should work if the data are located in worksheets with tab names and anchor cells

that could be different to the names and anchor cells given here. That is, your code will work

even if the anchor cell is different than “A4” for the price breaks and different than “A4” for the

client list. In addition, your code should work even if the worksheet tab names are different than

“Products” and “Clients”. The tab name for worksheets with other product lists and other client

lists and the anchor cells should be read from cells A1 to A4 of a worksheet with tab name

Scenario. For this example the “Scenario” worksheet looks as follows:

Please provide VBA Excel Codes. So that there should not be any abuse/inappropriate complaints or conflicts.Thanks.

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 ProductT in an anchor cell (e.g., cell A4) and the Extra Discount and more than' values are two columns to the right from the last column with price breaks. Similarly, the Total Ondered Total Amount and Total Amount (with extra discounty 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 Minimum Order Quantity toget unitprice Amount dikcourt) $11.72 $11A2 $11.14 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 (ie 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 columnsto the right of the last 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 Jin the sum of the "Amount Before Extra Discount' over all products and depends on the quantities ordered as indicated in the worksheet "Products 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 G5in this example) applies in addition to the discounts obtained by the quantity ordered ofeach gadget (ie.. inthis example the 5% discount applies to each unit cost of the gadget bought bythe customer) Note that the headers in this worksheet are pre-specified for the Clients, and the product names but the headers for all other columns have to be appropriately generated and placed through VBA. That is, the cels populated by the program in allthe worksheets should be cleaned out every time the program is run and before calculating the new values.

Explanation / Answer

Private Sub CommandButton1_Click()

Dim quantity(31) As Integer
Dim i As Integer
Dim price_gadget1 As Double
Dim price_gadget2 As Double
Dim price_gadget3 As Double
Dim price_gadget(30) As Double

For i = 1 To 10
quantity(i) = Sheet2.Cells(i + 4, 2).Value
Next i
For i = 11 To 20
quantity(i) = Sheet2.Cells(i - 6, 3).Value
Next i
For i = 21 To 30
quantity(i) = Sheet2.Cells(i - 16, 4).Value
Next i

For i = 1 To 10
If quantity(i) >= 0 And quantity(i) < 100 Then
price_gadget(i) = Sheet1.Cells(5, 2).Value
ElseIf quantity(i) >= 100 And quantity(i) < 500 Then
price_gadget(i) = Sheet1.Cells(5, 3).Value
ElseIf quantity(i) >= 500 And quantity(i) < 1000 Then
price_gadget(i) = Sheet1.Cells(5, 4).Value
Else
price_gadget(i) = Sheet1.Cells(5, 5).Value
End If
Next i
For i = 11 To 20
If quantity(i) >= 0 And quantity(i) < 100 Then
price_gadget(i) = Sheet1.Cells(6, 2).Value
ElseIf quantity(i) >= 100 And quantity(i) < 500 Then
price_gadget(i) = Sheet1.Cells(6, 3).Value
ElseIf quantity(i) >= 500 And quantity(i) < 1000 Then
price_gadget(i) = Sheet1.Cells(6, 4).Value
Else
price_gadget(i) = Sheet1.Cells(6, 5).Value
End If
Next i
For i = 21 To 30
If quantity(i) >= 0 And quantity(i) < 100 Then
price_gadget(i) = Sheet1.Cells(7, 2).Value
ElseIf quantity(i) >= 100 And quantity(i) < 500 Then
price_gadget(i) = Sheet1.Cells(7, 3).Value
ElseIf quantity(i) >= 500 And quantity(i) < 1000 Then
price_gadget(i) = Sheet1.Cells(7, 4).Value
Else
price_gadget(i) = Sheet1.Cells(7, 5).Value
End If
Next i
Dim total_order As Integer
total_order = 0
For i = 1 To 10
total_order = total_order + quantity(i)
Next i
Sheet1.Cells(5, 9).Value = total_order
For i = 11 To 20
total_order = total_order + quantity(i)
Next i
Sheet1.Cells(6, 9).Value = total_order
'For i = 21 To 30
'total_order = total_order + quantity(i)
'Next i
Sheet1.Cells(7, 9).Value = total_order
Dim discount As Double
discount = Sheet1.Cells(5, 7).Value
Dim cal_price1 As Double
Dim cal_price2 As Double
Dim cal_price3 As Double
cal_price = 0
For i = 1 To 10
cal_price1 = cal_price1 + price_gadget(i) * quantity(i)
'cal_price = price_gadget1 * quantity1 + price_gadget2 * quantity2 + price_gadget3 * quantity3
Sheet2.Cells(i + 4, 6) = cal_price1
Next i
Sheet1.Cells(5, 10).Value = cal_price1
For i = 11 To 20
cal_price2 = cal_price2 + price_gadget(i) * quantity(i)
Sheet2.Cells(i - 6, 7) = cal_price2
Next i
Sheet1.Cells(6, 10).Value = cal_price2
For i = 21 To 30
cal_price = cal_price3 + price_gadget(i) * quantity(i)
Sheet2.Cells(i - 16, 8) = cal_price3
Next i
Sheet1.Cells(7, 10).Value = cal_price3
For i = 1 To 10
Sheet2.Cells(i + 4, 10).Value = Sheet2.Cells(i + 4, 6).Value + Sheet2.Cells(i + 4, 7).Value + Sheet2.Cells(i + 4, 8).Value
Next i
Dim Dis_price As Double
Dis_price = Sheet1.Cells(7, 7).Value
Dim new_price As Double
Dim caldis_price As Double
If (cal_price1 > Dis_price) Then
caldis_price = cal_price1 * discount / 100
new_price = cal_price1 - caldis_price
Sheet1.Cells(5, 11).Value = new_price
Else
Sheet1.Cells(5, 11).Value = 0
End If
If (cal_price2 > Dis_price) Then
caldis_price = cal_price2 * discount / 100
new_price = cal_price2 - caldis_price
Sheet1.Cells(6, 11).Value = new_price
Else
Sheet1.Cells(5, 11).Value = 0
End If
If (cal_price3 > Dis_price) Then
caldis_price = cal_price3 * discount / 100
new_price = cal_price3 - caldis_price
Sheet1.Cells(6, 11).Value = new_price
Else
Sheet1.Cells(5, 11).Value = 0
End If
End Sub

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.