Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Column to Inbput Box Variable
User's Group
This is only the second time I have ever worked with Excel so please have patience. I need to enter Filtering Criteria from an Input Box. Then match the contents of that variable to Column L (Will always be Column L) Because of what I eventually have to do I cannot use Autofilter. TestClass = InputBox("Enter ACT, International, S.A.T. or TAKS Classification") ? = What is the Format(Syntax) to compare the contents of TestClass to Column L. There will be eventually more code, I have to convert from another software application (That I have little knowledge of) to Excel. Thanks to all of ya' for assistance Granny |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Column to Inbput Box Variable
It's difficult to give you "the" answer without knowing how you want to
handle matches. You say you can't use AutoFilter, so we'll just assume you want matched cells 'noticed' for the moment. This code physically moves cell to cell down the column - there are definitely more efficient, faster ways to do this with Range objects, but since this is 2nd time out for you <g. Dim lastRow as Long 'you get your TestClass value as before here lastRow = Range("L" & Rows.Count).End(xlUp).Row 'if using Excel 2007, substitute: lastRow = Range("L" & Rows.CountLarge).End(xlUp).Row Range("L1").Select Do Until ActiveCell.Row lastRow If ActiveCell.Value = TestClass Then 'code to process matched data here End If ActiveCell.Offset(1, 0).Activate ' move to next row Loop "GrandMaMa" wrote: User's Group This is only the second time I have ever worked with Excel so please have patience. I need to enter Filtering Criteria from an Input Box. Then match the contents of that variable to Column L (Will always be Column L) Because of what I eventually have to do I cannot use Autofilter. TestClass = InputBox("Enter ACT, International, S.A.T. or TAKS Classification") ? = What is the Format(Syntax) to compare the contents of TestClass to Column L. There will be eventually more code, I have to convert from another software application (That I have little knowledge of) to Excel. Thanks to all of ya' for assistance Granny |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Column to Inbput Box Variable
J. Latham;
Thanks for getting back. Earlier I needed some recommendations, but no one got back to me. I will be need to print a report on selected students. The first criteria is to select all of the test criteria. With your help I have part of the first step completed. There are 15 more conditions I must test for. Question: Is it better to create another work sheet or just select the students I need from my current work sheet? Naturally I do not want to change any information in the worksheet, just get a print out of the selected students. Also is there a way I can use the AutoFilter in a Macro? If you are wondering why, the software vendor we have used for the past 15 years is going out of business. We are also converting from an IBM system to individuals PC's for the Guidance Department. Thanks Again; Granny "JLatham" wrote: It's difficult to give you "the" answer without knowing how you want to handle matches. You say you can't use AutoFilter, so we'll just assume you want matched cells 'noticed' for the moment. This code physically moves cell to cell down the column - there are definitely more efficient, faster ways to do this with Range objects, but since this is 2nd time out for you <g. Dim lastRow as Long 'you get your TestClass value as before here lastRow = Range("L" & Rows.Count).End(xlUp).Row 'if using Excel 2007, substitute: lastRow = Range("L" & Rows.CountLarge).End(xlUp).Row Range("L1").Select Do Until ActiveCell.Row lastRow If ActiveCell.Value = TestClass Then 'code to process matched data here End If ActiveCell.Offset(1, 0).Activate ' move to next row Loop "GrandMaMa" wrote: User's Group This is only the second time I have ever worked with Excel so please have patience. I need to enter Filtering Criteria from an Input Box. Then match the contents of that variable to Column L (Will always be Column L) Because of what I eventually have to do I cannot use Autofilter. TestClass = InputBox("Enter ACT, International, S.A.T. or TAKS Classification") ? = What is the Format(Syntax) to compare the contents of TestClass to Column L. There will be eventually more code, I have to convert from another software application (That I have little knowledge of) to Excel. Thanks to all of ya' for assistance Granny |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Column to Inbput Box Variable
Turn on the macro recorder (tools=macro=record a new macro). Choose this
workbook for the location. apply the autofilter manually. Turn off the macro recorder by repeating the above or if the floating toolbar has been produced, use that. now go to the VBE and examine the code. It will show you how to apply an autofilter using code. If you have multiple complex conditions, it is often useful to put a formula in the next available column that can evaluate several conditions to return a true or false to indicate if that row should be included or not. If you are doing this in code, you can set up all the conditions, then use a technique such as JLatham has shown or autofilter on that column(s). The advanced filter can allow you to specify multiple conditions, however, another way may be to copy all the data to another sheet, then continue to cull it down as conditions are specified (delete rows that don't meet that condition - assume all conditions are AND related). The alternative to copying to another sheet and would be to hide rows in the exitsting sheet. I don't think it really matters one way or another whethe you do it inplace or copy to another sheet. Obviously working on a copy means the original should never get contaminated. -- Regards, Tom Ogilvy "GrandMaMa" wrote: J. Latham; Thanks for getting back. Earlier I needed some recommendations, but no one got back to me. I will be need to print a report on selected students. The first criteria is to select all of the test criteria. With your help I have part of the first step completed. There are 15 more conditions I must test for. Question: Is it better to create another work sheet or just select the students I need from my current work sheet? Naturally I do not want to change any information in the worksheet, just get a print out of the selected students. Also is there a way I can use the AutoFilter in a Macro? If you are wondering why, the software vendor we have used for the past 15 years is going out of business. We are also converting from an IBM system to individuals PC's for the Guidance Department. Thanks Again; Granny "JLatham" wrote: It's difficult to give you "the" answer without knowing how you want to handle matches. You say you can't use AutoFilter, so we'll just assume you want matched cells 'noticed' for the moment. This code physically moves cell to cell down the column - there are definitely more efficient, faster ways to do this with Range objects, but since this is 2nd time out for you <g. Dim lastRow as Long 'you get your TestClass value as before here lastRow = Range("L" & Rows.Count).End(xlUp).Row 'if using Excel 2007, substitute: lastRow = Range("L" & Rows.CountLarge).End(xlUp).Row Range("L1").Select Do Until ActiveCell.Row lastRow If ActiveCell.Value = TestClass Then 'code to process matched data here End If ActiveCell.Offset(1, 0).Activate ' move to next row Loop "GrandMaMa" wrote: User's Group This is only the second time I have ever worked with Excel so please have patience. I need to enter Filtering Criteria from an Input Box. Then match the contents of that variable to Column L (Will always be Column L) Because of what I eventually have to do I cannot use Autofilter. TestClass = InputBox("Enter ACT, International, S.A.T. or TAKS Classification") ? = What is the Format(Syntax) to compare the contents of TestClass to Column L. There will be eventually more code, I have to convert from another software application (That I have little knowledge of) to Excel. Thanks to all of ya' for assistance Granny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare data in column A with column B to find duplicates | Excel Discussion (Misc queries) | |||
compare cells in column to criteria, then average next column cell | Excel Worksheet Functions | |||
compare dates using a variable | Excel Worksheet Functions | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
compare data from one column with another and compare result to yet another | Excel Programming |