I have an "orders" and "results" worksheet and am having trouble with the code.
<tbody>
</tbody>Sub Subtotal()
Dim Amountpurchased As Single
Dim CustomerID As Integer
Dim x As Integer
Dim ResultRow As Integer
With Sheet2.Range("A3:C549")
ResultRow = 0
x = 1
For CustomerID = 101 To 199
Amountpurchased = 0
Do While CustomerID = .Offset(x, 1).Value
Amountpurchased = Amountpurchased + .Offset(x, 2)
x = x + 1
Loop
If Amountpurchased > 1500 Then
ResultRow = ResultRow + 1
With Sheet3.Range("A1:B101")
.Offset(ResultRow, 0).Value = CustomerID
.Offset(ResultRow, 1).Value = Amountpurchased
End With
End If
Next CustomerID
End With
With Sheet3
.Range("A1:B101").Sort KEY1:=Range("B1:B101"), Order1:=xlAscending, Header:=xlYes
End With
End Sub
The "Orders" worksheet contains the info about order date, customer identification & order amount. Since customers ordered more than once, there are multiple entries of customer IDs.
<tbody> </tbody> |
<tbody>
</tbody>
Dim Amountpurchased As Single
Dim CustomerID As Integer
Dim x As Integer
Dim ResultRow As Integer
With Sheet2.Range("A3:C549")
ResultRow = 0
x = 1
For CustomerID = 101 To 199
Amountpurchased = 0
Do While CustomerID = .Offset(x, 1).Value
Amountpurchased = Amountpurchased + .Offset(x, 2)
x = x + 1
Loop
If Amountpurchased > 1500 Then
ResultRow = ResultRow + 1
With Sheet3.Range("A1:B101")
.Offset(ResultRow, 0).Value = CustomerID
.Offset(ResultRow, 1).Value = Amountpurchased
End With
End If
Next CustomerID
End With
With Sheet3
.Range("A1:B101").Sort KEY1:=Range("B1:B101"), Order1:=xlAscending, Header:=xlYes
End With
End Sub