Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop VBA request
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop VBA request
On Apr 25, 2:29*am, Seeker wrote:
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Another avoid the loop trick request - on fast fill (Maybe an Alan trick) | Excel Programming |