Advanced Filter

[Chris Hankin]

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 ***

 

[merjet]

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

 

[Chris Hankin]

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 ***

 

[Chris Hankin]

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 ***

 

[merjet]

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

 

[Chris Hankin]

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 ***

 

[Chris Hankin]

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 ***