Query on Filtering my Rent Comparability Data

[golf4]

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

 

[acw]

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]

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]

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. Any suggestions on how I can resolve this?

Thanks so much for the help,

Golf

 

[golf4]

DUHHHHH!!!! 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... )

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. ray:

Thanks again,

Golf

 

[dk]

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.

    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]

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]

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]

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]

Hi,

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

HTH,
Dan