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" wrote in message
m...
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").Spec ialCells(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,




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---