Query on Filtering my Rent Comparability Data

golf4

Active Member
Joined
Jul 8, 2002
Messages
452
Hi everyone -

After quite a bit of digging, searching and messing with coding (you notice that I came up with before I had to come and beg and crawl for help as scary as that is), I've come up with the following code strips:

Private Sub CommandButton1_Click()
Dim str As String
str = InputBox("Enter The CITY You Are Searching For")
Selection.AutoFilter Field:=2, Criteria1:="=*" & str & "*", Operator:=xlAnd
str = InputBox("Enter The TYPE Of Unit You Are Searching For")
Selection.AutoFilter Field:=3, Criteria1:="=*" & str & "*", Operator:=xlAnd
str = InputBox("Enter The BR SIZE You Are Searching For")
Selection.AutoFilter Field:=8, Criteria1:="=*" & str & "*", Operator:=xlAnd
str = InputBox("Enter The RENT You Are Searching For")
Selection.AutoFilter Field:=6, Criteria1:="=*" & str & "*", Operator:=xlAnd
End Sub


The current set-up of my Rent Comparability Tool is Sheet1 (series of command buttons to open userforms), Sheet2 (actual database of unit information, rents, unit types, cities, etc.) and Sheet3 (various unimportant stuff).

I've got one of my command buttons set (via the code above) to filter the database depending on the "answers" the user enters in the Input Boxes. The problem I'm having is that the database will only filter if I activate the userform while in Sheet2 (the actual database sheet). What actually looking to have happen is to activate the userform (Input Boxes) while in Sheet1 and have the filtered data appear in Sheet1. This way, I can protect the actual database from being deleted or changed by mistake.

Any suggestions on how I can modify my code would be fantastic.

Thanks again, to everyone, for the help.

Golf
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

If you name the data range and change to a form command button rather than a control toolbox commandbutton, then the following code put in a normal module seemed to work. I've got the database on sheet 1, but the button on sheet 2.

Dim str As String
str = InputBox("Enter The CITY You Are Searching For", , "City1")
Sheets("sheet1").Range("dataa").AutoFilter Field:=2, Criteria1:="=*" & str & "*", Operator:=xlAnd
str = InputBox("Enter The TYPE Of Unit You Are Searching For", , "High")
Sheets("sheet1").Range("dataa").AutoFilter Field:=3, Criteria1:="=*" & str & "*", Operator:=xlAnd
str = InputBox("Enter The BR SIZE You Are Searching For", , 3)
Sheets("sheet1").Range("dataa").AutoFilter Field:=4, Criteria1:="=*" & str & "*", Operator:=xlAnd

Sheets("sheet2").Columns("a:d").Clear
Sheets("sheet1").Range("dataa").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("sheet2").Range("a1")



Tony
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi mate,

Change 'Selection' to Sheets("Sheet2").UsedRange

You can usually always avoid having to select anything with programming with Excel. This is good practice because selecting objects can slow your code (but in this case it wouldn't make much difference).
 

golf4

Active Member
Joined
Jul 8, 2002
Messages
452
Thanks for the response, Tony and Dan -

Dan - a follow up question on your suggestion. I've tried your suggestion:

Private Sub CommandButton1_Click()
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 TYPE Of Unit You Are Searching For")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=3, Criteria1:="=*" & str & "*", Operator:=xlAnd
str = InputBox("Enter The BR SIZE You Are Searching For")

Sheets("Comparability_Data").UsedRange.AutoFilter Field:=8, Criteria1:="=*" & str & "*", Operator:=xlAnd

str = InputBox("Enter The RENT You Are Searching For")
Sheets("Comparability_Data").UsedRange.AutoFilter Field:=6, Criteria1:="=*" & str & "*", Operator:=xlAnd
End Sub

The button on Sheet1 to filter that data in the database on Sheet2 (renamed as "Comparability_Data") works fine except for one thing. For some reason, the code strip for the BR Size (bolded above) doesn't seem to be working. When I try to filter, using the Input Box, by this criteria, all of the data disappears. The column for BR Size is, as far as I can see, in Column #8, and the filter triangle symbol in the column indicates that it is being filtered. All of the other filters seem to be working ok. :oops: Any suggestions on how I can resolve this?

Thanks so much for the help,

Golf
 

golf4

Active Member
Joined
Jul 8, 2002
Messages
452

ADVERTISEMENT

DUHHHHH!!!! :oops: Must be too late at night to work on this. I think I figured out my problem. The filtering code I'm using is based on text strings --- it seems whenever I try to filter a column with numbers, the filtering goes.... BLAH!!! (The columns with RENTS and BR Size are numbers and not text strings... :oops: :oops: :oops: )

Now that, I think, I've figured out the problem, I'm wondering how I can modify the code to filter some of my columns with numbers and not text strings? Any suggestions would be GREAT. :pray:

Thanks again,

Golf
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

What is it that you want to be returned by the filter? If you search for everything that contains 50 then you'd be looking at $50, $350, $200.50 and so on and you can't use wildcards with numbers (as you've discovered). How about asking for a minimum and maximum value and using this to filter your data e.g.

Code:
    cryMax = InputBox("Enter maximum rent")
    cryMin = InputBox("Enter minimum rent")

    Sheets("Comparability_Data").UsedRange.AutoFilter Field:=1, _
            Criteria1:="<" & cryMax, Operator:=xlAnd, Criteria2:=">" & cryMin

You could also maybe enter one value and then get the filter to return values within a certain range of the entered value e.g. +/- 10% or +/- $50.

Hope that gives you some ideas :)

Dan
 

golf4

Active Member
Joined
Jul 8, 2002
Messages
452

ADVERTISEMENT

Hi, Dan -

Thanks so much much for the help. The MAX and MIN was one of the options I was grasping for, but didn't want to push my luck by asking too many questions. :wink:

I'll try your suggestion when I get home tonight.


Thanks again,

Golf
 

golf4

Active Member
Joined
Jul 8, 2002
Messages
452
Hi, Dan -

Just wanted to thank you again for your help on my coding issue. Took your suggestion and built a little on (hope you don't mind :) .

Just in case anyone else is interested:

Code:
_______________________________________________________________________

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

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

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

Sheets("sheet1").Columns("A:AB").Clear
Sheets("Comparability_Data").Range("a2:AB16").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("sheet1").Range("a19")

End Sub

______________________________________________________________________

Thanks again,

Golf
 

golf4

Active Member
Joined
Jul 8, 2002
Messages
452
Hi, Dan and everyone -

A quick follow-up question on the code below related to the Max & Min range element. I decided to broach the project with the boss (don't kid yourself: suckin' up for brownie points), and she asked whether there was a way to keep the Search ability (via filter), but adjust the range slightly. What seems to be happening is when I enter 500 for the Max Rent and 300 for the Min Rent, the filtered range from my database is showing rent amounts from 301 through 499 --- logical, yes; but I don't feel real comfortable that Staff can work with this. Nor do I get a warm and fuzzy to have Staff remember to enter 501 as a Max and 299 as a Min to get the "full" range of rents from 300 to 500.

I've tried to mess with the bolded code strips to accomplish getting a full range of rents, based on my filter criteria, but to no avail. I was hoping that you or someone can give me a litle help in modifying my code:

Code:
_______________________________________________________________________

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

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

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


Sheets("sheet1").Columns("A:AB").Clear
Sheets("Comparability_Data").Range("a2:AB16").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("sheet1").Range("a19")

End Sub
______________________________________________________________________

I'll probably use the same assistance to modify the code strip for the BR Size as well.


Thanks for the help,

Golf
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

Rather than using "<" and ">", use "<=" and ">="

HTH,
Dan
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,101
Messages
5,768,101
Members
425,453
Latest member
bince

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top