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

 

[golf4]

Thanks again, Dan -

Ya know --- before I posted anything, I thought I had tried everything... even throwing in some "=" signs. It must be getting late here... brain fart blocked the whole idea of "<=" and ">=".

Thanks again,

Golf

 

[golf4]

Dan -

Just tried your suggestion and works great!!!!!

I was hoping I could pick your brain one more time :wink: :

Believe it or not, I understand just about all the code and how it works. The one last question I had is whether it is possible, once the user completes their data entry in the input boxes, i.e. BR Size range, Rent range, etc., to indicate (on Sheet1) the parameters that the user entered to filter the data? What I mean is, say, the user entered DALLAS as the City, SFH as the Unit Type, BR range as 2 - 5 and the Rent range as 300 - 500, is it possible to show these parameters on Sheet1 right above the filtered data results? This would come in REAL handy for file documentation when we print the comparability results out.

Thanks so much and take care,

Golf

 

[Smitty]

Heya Golf,

You should probably Dim Dan's cryMax & cryMin variables.

As for your last question re: showing the AutoFiler criteria, it would be possible, but you'd have to insert a row above. If the info will be printed out, would it work for you to capture the info in the Page Header or Footer? That way there'd be no insert row issues.

Just a thought,

Smitty

brain fart blocked the whole idea of

Dontcha just love those?

Have a great weekend!

 

[golf4]

Hi, Smitty -

Thanks for the response. The header-type thing sounds good, but I need to check with the boss to see what exactly she want to print out. Right now, I've got some 28 cells across full of data. What stuff she wants to appear is anyone's guess. I'll check with her on Monday.

BRAIN FARTS --- they seem to happen more when I'm on-line trying to work through VB issues at, around, 12:00 midnight...... maybe Oregon rain on the brain???

Take care,

Golf

 

[golf4]

Hi, Smitty -

Just a quick follow-up on one of our last threads. The boss indicated that it would be ok to have the search criteria stuff show in a footer. I was hoping you could point me in the right direction so I can make this happen.

Thanks again,

Golf