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 |
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 |
All times are GMT +1. The time now is 05:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com