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

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

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

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