Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How do I show Auto-Filter "Search" Criteria on Sheet1?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27
Default How do I show Auto-Filter "Search" Criteria on Sheet1?
Hi, everyone -

I have a quick query that I was hoping to get some help with. I have
created a rent comparability tool, for my employer, that uses the
Auto-Filter to filter a database of housing unit information
("Comparability_Data") using 4 filter criteria, and shows the filtered
results in Sheet1. I have included the code below:

Private Sub CommandButton1_Click() 'Search for Comparable units'
Dim str As String
str = InputBox("Enter The CITY You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=2,
Criteria1:="=*" & str & "*", Operator:=xlAnd

str = InputBox("Enter The UNIT TYPE You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=3,
Criteria1:="=*" & str & "*", Operator:=xlAnd

cryMin = InputBox("Enter The MINIMUM BR SIZE Of Unit:")
cryMax = InputBox("Enter The MAXIMUM BR SIZE Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=8,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM RENT AMOUNT You Are Searching
For:")
cryMax = InputBox("Enter The MAXIMUM RENT AMOUNT You Are Searching:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=6,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM YEAR BUILT of Unit:")
cryMax = InputBox("Enter The MAXIMUM YEAR BUILT Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=9,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

Sheets("sheet1").Range("A19:Ac39").Clear
Sheets("Comparability_Data").Range("a1:I16").Speci alCells(xlCellTypeVisible).Copy
Destination:=Sheets("sheet1").Range("a19")

Unload UserForm2
Sheets("SHEET1").Range("A1").Select
End Sub

My question is how would I modify the code to identify the filter
criteria used so that it would also appear on Sheet1? I was thinking,
maybe, in the footer or somewhere else visible on Sheet1.

Any help would be great --- Thanks!!!

Golf
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default How do I show Auto-Filter "Search" Criteria on Sheet1?
http://j-walk.com/ss/excel/usertips/tip044.htm

to get it to refresh:

=FilterCriteria(B5)&left(Subtotal(9,B5:B200),0)


this is one I wrote back in 2000

Here is a user defined function that will display the criteria in a cell:

Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function

=ShowFilter(B5)&left(Subtotal(9,B5:B200),0)

would show the filter for column 2

I usually put these functions in cells above the filter

--
Regards,
Tom Ogilvy



"golf4" wrote in message
om...
Hi, everyone -

I have a quick query that I was hoping to get some help with. I have
created a rent comparability tool, for my employer, that uses the
Auto-Filter to filter a database of housing unit information
("Comparability_Data") using 4 filter criteria, and shows the filtered
results in Sheet1. I have included the code below:

Private Sub CommandButton1_Click() 'Search for Comparable units'
Dim str As String
str = InputBox("Enter The CITY You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=2,
Criteria1:="=*" & str & "*", Operator:=xlAnd

str = InputBox("Enter The UNIT TYPE You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=3,
Criteria1:="=*" & str & "*", Operator:=xlAnd

cryMin = InputBox("Enter The MINIMUM BR SIZE Of Unit:")
cryMax = InputBox("Enter The MAXIMUM BR SIZE Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=8,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM RENT AMOUNT You Are Searching
For:")
cryMax = InputBox("Enter The MAXIMUM RENT AMOUNT You Are Searching:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=6,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM YEAR BUILT of Unit:")
cryMax = InputBox("Enter The MAXIMUM YEAR BUILT Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=9,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

Sheets("sheet1").Range("A19:Ac39").Clear

Sheets("Comparability_Data").Range("a1:I16").Speci alCells(xlCellTypeVisible)
..Copy
Destination:=Sheets("sheet1").Range("a19")

Unload UserForm2
Sheets("SHEET1").Range("A1").Select
End Sub

My question is how would I modify the code to identify the filter
criteria used so that it would also appear on Sheet1? I was thinking,
maybe, in the footer or somewhere else visible on Sheet1.

Any help would be great --- Thanks!!!

Golf


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default How do I show Auto-Filter "Search" Criteria on Sheet1?
Here is a similar one I wrote: (from a post by Debra Dalgleish)

Tom Ogilvy posted the following function, that can be used to return the
criteria from a column in an autofiltered table. It will show both
criteria if there are two, and will include the operator.

Public Function ShowFilter(rng As Range)
'UDF that displays the filter criteria.
'posted by Tom Ogilvy 1/17/02
'To make it respond to a filter change, tie it to the subtotal command.
'=showfilter(B10)&CHAR(SUBTOTAL(9,B11)*0+32)
'So the above would show the criteria for column B

Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function


About the same as the first, but has some comments.

--
Regards,
Tom Ogilvy



"golf4" wrote in message
om...
Hi, everyone -

I have a quick query that I was hoping to get some help with. I have
created a rent comparability tool, for my employer, that uses the
Auto-Filter to filter a database of housing unit information
("Comparability_Data") using 4 filter criteria, and shows the filtered
results in Sheet1. I have included the code below:

Private Sub CommandButton1_Click() 'Search for Comparable units'
Dim str As String
str = InputBox("Enter The CITY You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=2,
Criteria1:="=*" & str & "*", Operator:=xlAnd

str = InputBox("Enter The UNIT TYPE You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=3,
Criteria1:="=*" & str & "*", Operator:=xlAnd

cryMin = InputBox("Enter The MINIMUM BR SIZE Of Unit:")
cryMax = InputBox("Enter The MAXIMUM BR SIZE Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=8,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM RENT AMOUNT You Are Searching
For:")
cryMax = InputBox("Enter The MAXIMUM RENT AMOUNT You Are Searching:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=6,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM YEAR BUILT of Unit:")
cryMax = InputBox("Enter The MAXIMUM YEAR BUILT Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=9,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

Sheets("sheet1").Range("A19:Ac39").Clear

Sheets("Comparability_Data").Range("a1:I16").Speci alCells(xlCellTypeVisible)
..Copy
Destination:=Sheets("sheet1").Range("a19")

Unload UserForm2
Sheets("SHEET1").Range("A1").Select
End Sub

My question is how would I modify the code to identify the filter
criteria used so that it would also appear on Sheet1? I was thinking,
maybe, in the footer or somewhere else visible on Sheet1.

Any help would be great --- Thanks!!!

Golf


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,979
Default How do I show Auto-Filter "Search" Criteria on Sheet1?
Instead of reusing the variables, create unique variables, and show
those on Sheet1 --

'=====================================
Private Sub CommandButton1_Click() 'Search for Comparable units'
Dim strCity As String
Dim strUnit As String
Dim dblMinBr As Double
Dim dblMaxBr As Double
Dim dblMinRent As Double
Dim dblMaxRent As Double
Dim intMinYear As Integer
Dim intMaxYear As Integer

On Error Resume Next
Sheets("Comparability_Data").ShowAllData

strCity = InputBox("Enter The CITY You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=2, _
Criteria1:="=*" & strCity & "*", Operator:=xlAnd

strUnit = InputBox("Enter The UNIT TYPE You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=3, _
Criteria1:="=*" & strUnit & "*", Operator:=xlAnd

dblMinBr = InputBox("Enter The MINIMUM BR SIZE Of Unit:")
dblMaxBr = InputBox("Enter The MAXIMUM BR SIZE Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=8, _
Criteria1:="<=" & dblMaxBr, Operator:=xlAnd, _
Criteria2:="=" & dblMinBr

dblMinRent = InputBox("Enter The MINIMUM RENT AMOUNT You Are SearchingFor:")
dblMaxRent = InputBox("Enter The MAXIMUM RENT AMOUNT You Are Searching:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=6, _
Criteria1:="<=" & dblMaxRent, Operator:=xlAnd, _
Criteria2:="=" & dblMinRent

intMinYear = InputBox("Enter The MINIMUM YEAR BUILT of Unit:")
intMaxYear = InputBox("Enter The MAXIMUM YEAR BUILT Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=1, _
Criteria1:="<=" & intMaxYear, Operator:=xlAnd, _
Criteria2:="=" & intMinYear

Sheets("sheet1").Range("A19:Ac39").Clear
Sheets("Comparability_Data").Range("a1:I16") _
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("sheet1").Range("a19")

'Unload UserForm2
With Sheets("SHEET1")
.Range("A15").Value = "Min"
.Range("A16").Value = "Max"
.Range("B14").Value = "City"
.Range("C14").Value = "Unit Type"
.Range("D14").Value = "Br Size"
.Range("E14").Value = "Rent"
.Range("F14").Value = "Year"
.Range("B15").Value = strCity
.Range("C15").Value = strUnit
.Range("D15").Value = dblMinBr
.Range("E15").Value = dblMinRent
.Range("F15").Value = intMinYear
.Range("D16").Value = dblMaxBr
.Range("E16").Value = dblMaxRent
.Range("F16").Value = intMaxYear
.Range("A1").Select
End With

End Sub
'======================================

golf4 wrote:
Hi, everyone -

I have a quick query that I was hoping to get some help with. I have
created a rent comparability tool, for my employer, that uses the
Auto-Filter to filter a database of housing unit information
("Comparability_Data") using 4 filter criteria, and shows the filtered
results in Sheet1. I have included the code below:

Private Sub CommandButton1_Click() 'Search for Comparable units'
Dim str As String
str = InputBox("Enter The CITY You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=2,
Criteria1:="=*" & str & "*", Operator:=xlAnd

str = InputBox("Enter The UNIT TYPE You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=3,
Criteria1:="=*" & str & "*", Operator:=xlAnd

cryMin = InputBox("Enter The MINIMUM BR SIZE Of Unit:")
cryMax = InputBox("Enter The MAXIMUM BR SIZE Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=8,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM RENT AMOUNT You Are Searching
For:")
cryMax = InputBox("Enter The MAXIMUM RENT AMOUNT You Are Searching:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=6,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM YEAR BUILT of Unit:")
cryMax = InputBox("Enter The MAXIMUM YEAR BUILT Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=9,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

Sheets("sheet1").Range("A19:Ac39").Clear
Sheets("Comparability_Data").Range("a1:I16").Speci alCells(xlCellTypeVisible).Copy
Destination:=Sheets("sheet1").Range("a19")

Unload UserForm2
Sheets("SHEET1").Range("A1").Select
End Sub

My question is how would I modify the code to identify the filter
criteria used so that it would also appear on Sheet1? I was thinking,
maybe, in the footer or somewhere else visible on Sheet1.

Any help would be great --- Thanks!!!

Golf


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27
Default How do I show Auto-Filter "Search" Criteria on Sheet1?
Hi, Tom and Debra -

Thanks for your responses. I'll give your suggestions a try and let you know.

Thanks again,
Golf


Debra Dalgleish wrote in message ...
Instead of reusing the variables, create unique variables, and show
those on Sheet1 --

'=====================================
Private Sub CommandButton1_Click() 'Search for Comparable units'
Dim strCity As String
Dim strUnit As String
Dim dblMinBr As Double
Dim dblMaxBr As Double
Dim dblMinRent As Double
Dim dblMaxRent As Double
Dim intMinYear As Integer
Dim intMaxYear As Integer

On Error Resume Next
Sheets("Comparability_Data").ShowAllData

strCity = InputBox("Enter The CITY You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=2, _
Criteria1:="=*" & strCity & "*", Operator:=xlAnd

strUnit = InputBox("Enter The UNIT TYPE You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=3, _
Criteria1:="=*" & strUnit & "*", Operator:=xlAnd

dblMinBr = InputBox("Enter The MINIMUM BR SIZE Of Unit:")
dblMaxBr = InputBox("Enter The MAXIMUM BR SIZE Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=8, _
Criteria1:="<=" & dblMaxBr, Operator:=xlAnd, _
Criteria2:="=" & dblMinBr

dblMinRent = InputBox("Enter The MINIMUM RENT AMOUNT You Are SearchingFor:")
dblMaxRent = InputBox("Enter The MAXIMUM RENT AMOUNT You Are Searching:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=6, _
Criteria1:="<=" & dblMaxRent, Operator:=xlAnd, _
Criteria2:="=" & dblMinRent

intMinYear = InputBox("Enter The MINIMUM YEAR BUILT of Unit:")
intMaxYear = InputBox("Enter The MAXIMUM YEAR BUILT Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=1, _
Criteria1:="<=" & intMaxYear, Operator:=xlAnd, _
Criteria2:="=" & intMinYear

Sheets("sheet1").Range("A19:Ac39").Clear
Sheets("Comparability_Data").Range("a1:I16") _
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("sheet1").Range("a19")

'Unload UserForm2
With Sheets("SHEET1")
.Range("A15").Value = "Min"
.Range("A16").Value = "Max"
.Range("B14").Value = "City"
.Range("C14").Value = "Unit Type"
.Range("D14").Value = "Br Size"
.Range("E14").Value = "Rent"
.Range("F14").Value = "Year"
.Range("B15").Value = strCity
.Range("C15").Value = strUnit
.Range("D15").Value = dblMinBr
.Range("E15").Value = dblMinRent
.Range("F15").Value = intMinYear
.Range("D16").Value = dblMaxBr
.Range("E16").Value = dblMaxRent
.Range("F16").Value = intMaxYear
.Range("A1").Select
End With

End Sub
'======================================

golf4 wrote:
Hi, everyone -

I have a quick query that I was hoping to get some help with. I have
created a rent comparability tool, for my employer, that uses the
Auto-Filter to filter a database of housing unit information
("Comparability_Data") using 4 filter criteria, and shows the filtered
results in Sheet1. I have included the code below:

Private Sub CommandButton1_Click() 'Search for Comparable units'
Dim str As String
str = InputBox("Enter The CITY You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=2,
Criteria1:="=*" & str & "*", Operator:=xlAnd

str = InputBox("Enter The UNIT TYPE You Are Searching For:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=3,
Criteria1:="=*" & str & "*", Operator:=xlAnd

cryMin = InputBox("Enter The MINIMUM BR SIZE Of Unit:")
cryMax = InputBox("Enter The MAXIMUM BR SIZE Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=8,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM RENT AMOUNT You Are Searching
For:")
cryMax = InputBox("Enter The MAXIMUM RENT AMOUNT You Are Searching:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=6,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

cryMin = InputBox("Enter The MINIMUM YEAR BUILT of Unit:")
cryMax = InputBox("Enter The MAXIMUM YEAR BUILT Of Unit:")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=9,
Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:="=" & cryMin

Sheets("sheet1").Range("A19:Ac39").Clear
Sheets("Comparability_Data").Range("a1:I16").Speci alCells(xlCellTypeVisible).Copy
Destination:=Sheets("sheet1").Range("a19")

Unload UserForm2
Sheets("SHEET1").Range("A1").Select
End Sub

My question is how would I modify the code to identify the filter
criteria used so that it would also appear on Sheet1? I was thinking,
maybe, in the footer or somewhere else visible on Sheet1.

Any help would be great --- Thanks!!!

Golf
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
"search" a different sheet with multiple criteria Art Excel Worksheet Functions 2 March 10th 10 08:11 PM
How to show "yes" or "No" in a cell if certain criteria is met? Bojan New Users to Excel 3 September 28th 08 10:36 PM
Auto Filter - how to collapse "non-blank" cells in a column? marla Excel Discussion (Misc queries) 2 August 24th 08 10:03 PM
how to auto "copy & paste" (or filter) from a Master spreadsheet? Armando Excel Worksheet Functions 0 September 13th 06 08:55 PM
Excel will not "show all" from an Advanced Filter on a sheet with protected cells. Gareth Surgenor Excel Programming 3 May 20th 04 10:48 PM


All times are GMT +1. The time now is 12:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement