Question on Input Boxes and Auto-Filter

[golf4]

Hi, everyone -

I am currently developing a Rent Comparability Tool that we can use at
work to compare market housing units to units that families are
proposing to rent on the Section 8 Voucher Program. One of the program
requirements is that an evaluation be made as to whether the rent a
landlord is proposing to charge a Section 8 family is comparable to
other similar "unassisted" rental units.

What we have now is about 9 binders full of statistics on unassisted
rentals such as where the rentals are located (cities), rent amounts,
bedroom sizes, utilities, etc. Part of the project is to create the
database with all of this stuff located on Sheet2 (called
"Comparability_Data"), which, for the most part, is done. Userforms
have already been built to enter future units into the database from
Sheet1. As well, a second userform has been built to do the actual
Search for comparables based on user input into the 6 Input Boxes
indicated in the code below via filtering Sheet2 to match the
unassisted units the proposed Section 8 unit. With some great
assistance from others, I've been able to come up with the code below
that seems to work fantastically.

This is my question/problem: the use of the Max and Min to have Staff
enter the rent range. 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 code strips to accomplish getting a FULL
range of rents, based on my filter criteria, but to no avail. I was
hoping that 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
bedroom size as well.


Thanks for the help,

 

[Nigel]

You could change the criteria from

Criteria1:="<" & cryMax, Operator:=xlAnd, Criteria2:=">" & cryMin

to

Criteria1:="<=" & cryMax, Operator:=xlAnd, Criteria2:=">=" & cryMin

alternatively add or substract an offset value to your criteria to ensure
your range is inclusive eg

cryMax = cryMax + 1
cryMin = CryMin - 1

You might want to check if the input is an integer value before you test the
data in case someone enters a fractional dollar amout eg 500.01, so use

cryMax = Int(cryMax)

You might also want to protect yourself from some bright spark who enters a
negative dollar amount, so use the absolute function to force a postive
value eg

cryMax = Int(Abs(cryMax))

Do not put in Abs(Int(cryMax)) as for a negative number it will round down
(more negative), eg Int(-500.4) becomes -501, but Int(Abs(-500.4)) becomes
500

Hope that tops up your brownie points!

Cheers
Nigel

 

[golf4]

Hi, Nigel -

Thanks so much for the suggestions. Just tried them out and they work
great!!!

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

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

 

[Tom Ogilvy]

cryMax = 100: cryMin = 50
? "Criteria1:=""<=" & cryMax & """, Operator:=xlAnd, Criteria2:="">=" &
cryMin & """"
Criteria1:="<=100", Operator:=xlAnd, Criteria2:=">=50"

With Worksheets("sheet1")
.Range("A1").Value = "'<=" & cryMax
.Range("A2").Value = "'>=" & cryMin
End with

As an example.
--
Regards,
Toom Ogilvy

Hi, Nigel -

Thanks so much for the suggestions. Just tried them out and they work
great!!!

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

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




"Nigel" <[email protected]> wrote in message

Encryption =---

 

[Nigel]

Hi Golf

Cannot add much more than Tom Olgivy other than you can put the selection
crtieria values into a dedicated area on your worksheet with appropriate
headings and labelling. This way you can add other information that can
customise your report.

Good Luck
Cheers
Nigel

Hi, Nigel -

Thanks so much for the suggestions. Just tried them out and they work
great!!!

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

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




"Nigel" <[email protected]> wrote in message

Encryption =---

 

[golf4]

Thanks, Tom & Nigel -

I really appreciate the suggestions. Let me give them a shot at home,
and I'll let you know how they work.

Thanks again,

Golf

 

[golf4]

Hi, Tom -

Thanks for your suggestion. I was hoping I could ask a couple
follow-up questions. When I got home, I inserted your code strip:
_______________________________________________________________________________
With Worksheets("sheet1")

.Range("A1").Value = "'<=" & cryMax
.Range("A2").Value = "'>=" & cryMin
End with

_______________________________________________________________________________

It seemed to work great for one of my parameters. The thing is: I'm
currently using the cryMAX and cryMIN for at least three (3) different
filter parameters: BR Size, Rent and Year Built - all tied to my
database in different columns. Is there a way to modify your code
example so that I can copy over all three search parameters into
Sheet1?

The other quest is along the same line, but it involves text strings
as opposed to values. I'm lookin to do the same thing with the
InputBoxes where the user enters text rather than a range of values.

Any help you can give me would be fantastic.

Thanks for everything,

Golf

 

[Tom Ogilvy]

ans = Inputbox("get value 1")
Range("A3").Value = ans
ans = Inputbox("get value 2")
Range("A4").Value = ans
ans = Inputbox("get value 3")
Range("A5").Value = "'>=" & ans

so in general, when you get the value in a variable, write it to the
location of your choice

 

[golf4]

Hi, Tom -

Thanks for the help. I really appreciate your time and assistance.

Golf