Beginner VBA quesetion

[hutch27]

I have an "orders" and "results" worksheet and am having trouble with the code.

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. My task is to write a sub that finds those customers whose total order amount is greater than $1500, and show these customers' IDs and corresponding total order amount in the "results" worksheet, and also sort the results by total amount in ascending order. <tbody> </tbody>

<tbody>
</tbody>

I've got two codes but neither are working, i'll post the second one in a reply to this one


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

 

[hutch27]

This is the second code and this isn't working either


Option Explicit


Sub LargeOrder()


Dim cell1 As Range


Dim Cell2 As Range


Dim Cell3 As Range


Dim nLargeOrder As Integer


Dim amtLargeOrder As Currency


Const hiAmount = 1500


Dim iCustomer As Integer


Dim nCustomers As Integer


Dim CustomerID As String






Set cell1 = Sheet1.Range("A3")


Set Cell2 = Sheet2.Range("A3")



nLargeOrder = 0


With cell1


nCustomers = Range(.Offset(1, 0), .End(xlDown)).Rows.Count


If amtLargeOrder > hiAmount Then


nLargeOrder = nLargeOrder + 1

'Record the results on Sheet2.

Cell2.Offset(nLargeOrder, 0).Value = CustomerID

Cell2.Offset(nLargeOrder, 1).Value = amtLargeOrder

Cell2.Offset(nLargeOrder, 1).NumberFormat = "$#,##0"
End If





End With
Sheet3.Activate

Cell2.Select


End Sub