|
Question on Input Boxes and Auto-Filter
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, |
Question on Input Boxes and Auto-Filter
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 =--- |
Question on Input Boxes and Auto-Filter
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" wrote in message ... 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 =--- |
Question on Input Boxes and Auto-Filter
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 "golf4" wrote in message om... 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" wrote in message ... 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 =--- |
Question on Input Boxes and Auto-Filter
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 "golf4" wrote in message om... 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" wrote in message ... 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 =--- ----== 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 =--- |
Question on Input Boxes and Auto-Filter
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 "Nigel" wrote in message ... 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 "golf4" wrote in message om... 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" wrote in message ... 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 =--- ----== 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 =--- |
| All times are GMT +1. The time now is 08:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com