loop VBA request

  • Thread starter Thread starter Seeker
  • Start date Start date
S

Seeker

I have 3 columns contain names(unknown, may duplicated and dynamic row), date
and amount; I am looking a way to get a sum and count on data that:
1) date is equal to or greater than today, then
2) loop thru name and subtotal on each customer
3) sum subtotal if value is equal or above 10k
3) count 2) with same name treat as once
Tks
 
I have 3 columns contain names(unknown, may duplicated and dynamic row), date
and amount; I am looking a way to get a sum and count on data that:
1) date is equal to or greater than today, then
2) loop thru name and subtotal on each customer
3) sum subtotal if value is equal or above 10k
3) count 2) with same name treat as once
Tks
Click to expand...

Give this a look. Wasn't sure of your layout so I used column(a) for
the date, b=client, and c=amount.
It asks the user for a date then copies all rows where the date is
equal/after the user's input to sheet2 where it sorts and subtotals by
client so your original data remains unchanged.

Sub test()
FindDate = CDate(InputBox("Enter date"))
Cells.AutoFilter Field:=1, Criteria1:=">=" & FindDate
Cells.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
Application.CutCopyMode = False
Cells.AutoFilter 'turn off filter
Worksheets("Sheet2").Activate
Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYesr
lastrow = Range("C65536").End(xlUp).Row
Range("A1", "C" & lastrow).Subtotal GroupBy:=2, Function:=xlSum,
TotalList:=Array(3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
 
Back
Top