Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter
Hello,
Could someone please help me with the following? I am trying to create two separate excel advanced filters: 1st Filter: My worksheet is named: DPC Expenses. I wish to create an advanced filter that prompts the user for a date range (eg 01 - 31 Jan 07) and also prompts the user for a single Cost Element Code (eg 21008, 21019, 21452, 21097 etc...). After the user has entered the required information, the advanced filter will need to look up column I (the Cost column) and add up all the costs that are filtered. The Date Ordered column is column B. The Cost Element Code column is column K. The Cost column is column I. I wish the output to be shown on another worksheet named: Query (which is in the same workbook). On the Query wooksheet, the user should see the date range and cost element code entered and the total cost. 2nd Filter: My worksheet is named: DPC Expenses. I wish to create an advanced filter that prompts the user for a date range (eg 01 - 31 Jan 07) only. After the user has entered the required information, the advanced filter will need to look up column I (the Cost column) and add up all the costs that are filtered. The Date Ordered column is column B. The Cost column is column I. I wish the output to be shown on another worksheet named: Query (which is in the same workbook). On the Query wooksheet, the user should see the date range and the total cost. Any help on this would be greatly appreciated. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter
This will do both.
Hth, Merjet Sub Query() Dim dtStart As Date Dim dtEnd As Date Dim aCode As String Dim iEnd As Long Dim dSum As Double Dim c As Range Dim rng As Range Dim ws As Worksheet Set ws = Sheets("DPC Expenses") ws.Activate iEnd = ws.Range("B2").End(xlDown).Row Set rng = ws.Range("B2:B" & iEnd) dtStart = InputBox("Enter start date (mm/dd/yy).") dtEnd = InputBox("Enter end date (mm/dd/yy).") aCode = InputBox("Enter code. Leave blank for all.") For Each c In rng If c = dtStart And c <= dtEnd Then If aCode = "" Then dSum = dSum + c.Offset(0, 7) If aCode = c.Offset(0, 9) Then dSum = dSum + c.Offset(0, 7) End If Next c Sheets("Query").Range("A2") = dtStart Sheets("Query").Range("B2") = dtEnd If aCode = "" Then Sheets("Query").Range("C2") = "all" Else Sheets("Query").Range("C2") = aCode End If Sheets("Query").Range("D2") = dSum Sheets("Query").Activate End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter
Thankyou very mucg Merjet - I really appreciate all the hard work and
effort you put in to help me. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter
Hello Merjet,
I used your VBA code you provided and unfortunately ran into a slight snag. You code was: Sub Query() Dim dtStart As Date Dim dtEnd As Date Dim aCode As String Dim iEnd As Long Dim dSum As Double Dim c As Range Dim rng As Range Dim ws As Worksheet Set ws = Sheets("DPC Expenses") ws.Activate iEnd = ws.Range("B2").End(xlDown).Row Set rng = ws.Range("B2:B" & iEnd) dtStart = InputBox("Enter start date (mm/dd/yy).") dtEnd = InputBox("Enter end date (mm/dd/yy).") aCode = InputBox("Enter code. Leave blank for all.") For Each c In rng If c = dtStart And c <= dtEnd Then If aCode = "" Then dSum = dSum + c.Offset(0, 7) If aCode = c.Offset(0, 9) Then dSum = dSum + c.Offset(0,7) End If Next c Sheets("Query").Range("A2") = dtStart Sheets("Query").Range("B2") = dtEnd If aCode = "" Then Sheets("Query").Range("C2") = "all" Else Sheets("Query").Range("C2") = aCode End If Sheets("Query").Range("D2") = dSum Sheets("Query").Activate End Sub I tried to use the code to calculate the total cost between the dtStart: 08/01/06 (01 Aug 06) and dtEnd: 08/30/06 (30 Aug 06) for aCode: 21008. The code summed the costs for July and August. I then changed the following code from: iEnd = ws.Range("B2").End(xlDown).Row Set rng = ws.Range("B2:B" & iEnd) to: iEnd = ws.Range("B8").End(xlDown).Row Set rng = ws.Range("B8:B" & iEnd). The code then successfully calculated the costs. However, this only worked for August 2006. My worksheet: DPC Expenses has a header row at row 2 and the data starts at row 3. Row 1 is blank at the moment - but it will be used for macro buttons. Could you please refine your code to make it work better? Thanks again for all your help and I sincerely hope that you may be able to help again. Many thanks, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter
The header being in row2 should make no difference. The code works
fine for me. Check the way you use dates carefully. Date calculations can be tricky. You may need to change the format the InputBox asks for. If you think the code is including some dates that it shouldn't, then put a breakpoint in the code and step through it line by line to discover why. Hth, Merjet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter
Thanks again Merjet,
I changed the date format in your code from mm/dd/yy to dd-mmm-yy and now it works perfectly. Cheers, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter
Hello,
Could someone please help me with the following for Excel 2003? I am trying to modify the following code shown below: My worksheet is named: DPC Expenses. I wish to create an advanced filter that prompts the user for the following: Date range - Start Date and End Date (eg 01 - 31 Jan 07) and Cost Element Code (eg 21008, 21019, 21452, 21097 etc...) and DPC Holder (eg Joe Bloggs)and Paid (Y/N). After the user has entered the required information, the advanced filter will need to look up column I (the Cost column) and add up all the costs that are filtered. The Date Ordered column is column B. The DPC Holder column is column J. The Cost Element Code column is column K. The Cost column is column I. The Paid (Y/N) column is column M. I wish the output to be shown on another worksheet named: Query (which is in the same workbook). On the Query wooksheet, the user should see the date range, the DPC Holder, Cost Element Code and Paid (Y/N) values entered and the total cost. Sub Query() Dim dtStart As Date Dim dtEnd As Date Dim aCode As String Dim dpcCode As String Dim iEnd As Long Dim dSum As Double Dim c As Range Dim d As Range Dim rng As Range Dim ws As Worksheet Set ws = Sheets("DPC Expenses") ws.Activate iEnd = ws.Range("B8").End(xlDown).Row Set rng = ws.Range("B8:B" & iEnd) dtStart = InputBox("Enter start date (dd-mmm-yy).") dtEnd = InputBox("Enter end date (dd-mmm-yy).") aCode = InputBox("Enter CEC code. Leave blank for all.") dpcCode = InputBox("Enter DPC code. Leave blank for all.") For Each c In rng If c = dtStart And c <= dtEnd Then If aCode = "" Then dSum = dSum + c.Offset(0, 7) If aCode = c.Offset(0, 9) Then dSum = dSum + c.Offset(0, 7) End If Next c For Each d In rng If d = dtStart And d <= dtEnd Then If dpcCode = "" Then dSum = dSum + d.Offset(0, 7) If dpcCode = d.Offset(0, 8) Then dSum = dSum + d.Offset(0, 7) End If Next d Sheets("Query").Range("A2") = dtStart Sheets("Query").Range("B2") = dtEnd If aCode = "" Then Sheets("Query").Range("C2") = "all" Else Sheets("Query").Range("C2") = aCode End If If dpcCode = "" Then Sheets("Query").Range("D2") = "all" Else Sheets("Query").Range("D2") = dpcCode End If Sheets("Query").Range("E2") = dSum Sheets("Query").Activate End Sub Any help on this would be greatly appreciated. Kind regards, Chris. Live Long and Prosper :-) *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter - filter rows < | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |