Using VBA a. (5)Use A1 as the anchor cell and put your code within a With...End

Question

Using VBA

a. (5)Use A1 as the anchor cell and put your code within a With...End With structure b. (5)Use a For Next loop to loop through the customers c. (5)Inside the For Next loop in step b, use another For Next Loop to loop through the products d. (8)Inside the For Next loop in step c, determine the unit price each customer should pay for each product, based on the amount he/she purchased for the product. CHint: You can use cases structure, or If statement, or a combination of For Next loop and If statement) e. (6)Calculate the price each customer should pay for each product based on the unit price from step d (6)Print the result from step e to the cells F8:H22. For example, the price that customer 1 should pay for Product is printed in F8 g. (5)Calculate the total price each customer should pay by summing up the price for each product, and print the total in cells I8:122

Explanation / Answer

VBA CODE

Dim i As Integer, j As Integer, k As Integer, x As Integer, sum As Single

Private Sub CommandButton1_Click()
For i = 8 To 22
sum = 0
For j = 2 To 4
x = Cells(i, j).Value
If (x >= Cells(2, 1).Value And x < Cells(3, 1).Value) Then
Select Case j
Case Is = 2
Cells(i, j + 4).Value = x * 8.92
Case Is = 3
Cells(i, j + 4).Value = x * 11.1
Case Is = 4
Cells(i, j + 4).Value = x * 12.5
End Select
End If

If (x >= Cells(3, 1).Value And x < Cells(4, 1).Value) Then
Select Case j
Case Is = 2
Cells(i, j + 4).Value = x * 8.45
Case Is = 3
Cells(i, j + 4).Value = x * 10.95
Case Is = 4
Cells(i, j + 4).Value = x * 12
End Select
End If

If (x >= Cells(4, 1).Value) Then
Select Case j
Case Is = 2
Cells(i, j + 4).Value = x * 8.2
Case Is = 3
Cells(i, j + 4).Value = x * 10.5
Case Is = 4
Cells(i, j + 4).Value = x * 11.85
End Select
End If
sum = sum + Cells(i, j + 4).Value
Next j
Cells(i, 9).Value = sum
Next i
End Sub

OUTPUT:

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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