AutoFilter - Lack or Experience

[Guest]

Sorry Guys, my lack of experience is showing again!

Here is my present code!

Dim aws as WorkSheet
Dim FrstCol as String
Dim ScndCol as String
Dim ThrdCol as String
Dim FrthCol as String
Dim Course as String

Set aws as ActiveSheet

Course = InputBox("Enter The Column Number For The Subject You Teach")

I need Just The Students who matches All of The Criteria Below.

To Be Eligible for European Literature, the student must have completed an
advanced course (FrstCol = Any Subject or Not Blank) and fall in the (ScndCol
= FGJKLPQR) Grading Criteria and must have a (ThrdCol = A or B Grade) and
show progress to theoretically have the ability to pass the three national
tests and international test (frthCol = GKLQ")

What I am getting are all students who fit the criteria of the (frthCol) or
GKLQ

I have tried all of the different and/or options I could think of, but I
keep getting syntax errors. My lack of experience is showing again!

With aws
.Columns(Course).AutoFilter Field:=1, Criteria1:="European Literature"
.Columns(FrstCol).AutoFilter Field:=1, Criteria1:="<>"
.Columns(ScndCol).AutoFilter Field:=1, Criteria1:="FGJKLPQR"
.Columns(ThrdCol).AutoFilter Field:=1, Criteria1:<"C"
.Columns(FrthCol).AutoFilter Field:=1, Criteria1:="GKLQ"
End With

Thanks Again

Granny

 

[Guest]

Your applying a new autofilter each time - that isn't how it works. You need
to set the criteria in the existing filter.

Sub ABC()
Dim aws As Worksheet
Dim Course As Long, FrstCol As Long, ScndCol As Long
Dim ThrdCol As Long, FrthCol As Long
Dim rng As Range
Set aws = ActiveSheet
Course = 8
FrstCol = 1
ScndCol = 2
ThrdCol = 3
FrthCol = 4
With aws
If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=Course, Criteria1:="European Literature"
.AutoFilter Field:=FrstCol, Criteria1:="<>"
.AutoFilter Field:=ScndCol, Criteria1:="FGJKLPQR"
.AutoFilter Field:=ThrdCol, Criteria1:="<C"
.AutoFilter Field:=FrthCol, Criteria1:="GKLQ"
End With

End With

Notice that the columns/Fields are Longs/Numbers, not strings. The are
counted relative to the upper left corner of the data table. I have assumed
A1, but if the table started in M30, then Field:=1 would refer to column M.

 

[Guest]

Tom;
Thanks for getting back to me.

I am a little confused, I understand some of what you are telling me, but
not all of it.

In reality I teach four courses. English Literature, European Literature,
and Sophomore English and Junior English.

Each Course is in a different column, why I do not know?

I need to enter the Column "Defined as Course" as one of my criteria. How
do I do this in your example?

The rest I think I understand.

Thanks for getting back, you are good!

Granny, the English Teacher!

 

[Guest]

The same way as you were doing it:

ABC()
Dim aws As Worksheet
Dim Course As Long, FrstCol As Long, ScndCol As Long
Dim ThrdCol As Long, FrthCol As Long
Dim rng As Range
Set aws = ActiveSheet
Course = InputBox("Enter The Column Number For The Subject You Teach")
FrstCol = 1
ScndCol = 2
ThrdCol = 3
FrthCol = 4
With aws
If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=Course, Criteria1:="European Literature"
.AutoFilter Field:=FrstCol, Criteria1:="<>"
.AutoFilter Field:=ScndCol, Criteria1:="FGJKLPQR"
.AutoFilter Field:=ThrdCol, Criteria1:="<C"
.AutoFilter Field:=FrthCol, Criteria1:="GKLQ"
End With

End With
End Sub

Just make sure you enter the course column as a number counted from the left
side of the autofilter range.

 

[Guest]

Tom;
After several hours I am still dead in the water. One The lines
marked with an * I am getting the following message!

Run Time Error '1004':

AutoFilter method of Range class failed. My code is exaclty the same as
ypurs except I have:

Dim NWB as Workbook " Creating a New Workbook!

Set NWB = Workbook.add

Thank for trying to help!

Granny

 

[Dave Peterson]

Add an "S"

Set NWB = WorkbookS.add

Tom;
After several hours I am still dead in the water. One The lines
marked with an * I am getting the following message!

Run Time Error '1004':

AutoFilter method of Range class failed. My code is exaclty the same as
ypurs except I have:

Dim NWB as Workbook " Creating a New Workbook!

Set NWB = Workbook.add

Thank for trying to help!

Granny

 

[Guest]

Dave;
Sorry the machine in the guidance department does not have Internet
capability so I have to re-key everything.

Typo on my part, I do have:

Set NWB = WorkbookS.add

Still having the same halts!

Thank for your concern!

Granny

 

[Dave Peterson]

I didn't see any reference to a workbook in either of Tom's post.

Maybe you changed more than you let on.

Can you transfer files via a floppy disk or a USB thumb drive or a CD or ....

 

[Guest]

Dave;
I have been at this for three weeks now and am no farther than when I
started! To say I am greatly discouraged is an understatement. I am been
glued to my PC looking at solutions in the User's groups (also others) and
still do not know If I can explain it or if it can be done!

The problem is that there is a set of criteria that must be
calcuulated to determine what Columns and always columns must be examined.
You start by entering what course you teach! That can be in eight columns, "
K, L, M, N O, P, Q, or R"

That is entered via an InputBox. You enter the Column into the box.
You then enter the course you want to schedule into another InputBox. I
teach four different Literature and English courses.

The system then analysizes each student (based on columns, not
cells) to determine if they are eligible and if the guidance department feels
that can get a passing grade in that course.

This application was on an IBM AS 400 and was used in over 4,000
districts in the U.S. The Microsoft Consulting Team (none of them have ever
programmed or taught in high school) have sold over 1,000 school districts
that this can be done using Excel. The smaller high schools (recommended by
the Gate's Foundation) do not have the financial resources to hire
experienced programmers so teachers like myself have to figure out how to do
it.

In all honesty my Macros are so messed up with patching from users
groups, that I am embarrassed to display what I have.

However it is all about filtering columns, not cells.

Enter a column, filter the records requested.

Enter another criteria, filter the records that match that criteria from the
lines (records) selected above.

Is the course an honors or advanced course?

Filter another unknown column and so on!

If any selected column has a blank, print the students name (there should
not be any blanks, but because we have to hire teachers if we need them, we
ignore the blanks and find out why information is missing on the student.

We than have to do a mail merge to contact the parent to see if they want
their child in the class.

One experienced "C" programmer has indicated via the users group that his
code is over 3,000. lines in a Macro, however I am pretty sure he is in a
very, very large high school.

Thanks for your concern and helping!

Granny

 

[Dave Peterson]

Maybe it's time to rethink your design.

Instead of asking the users to type in a column letter or number, you can
display the headers for the column in a combobox and they can choose what they
want.

Instead of typing the course you want to schedule, you choose from a combobox
that displays only the valid entries.

You could use a userform to get this kind of information. Your existing code
may have to be tweaked to accept the input via the userform, but it may make it
easier for the user.

Check Debra Dalgleish's site:
http://contextures.com/xlUserForm01.html

and these articles by Peter Aiken:
http://msdn2.microsoft.com/en-us/library/aa155729(office.10).aspx
http://msdn2.microsoft.com/en-us/library/aa155610(office.10).aspx

Dave;
I have been at this for three weeks now and am no farther than when I
started! To say I am greatly discouraged is an understatement. I am been
glued to my PC looking at solutions in the User's groups (also others) and
still do not know If I can explain it or if it can be done!

The problem is that there is a set of criteria that must be
calcuulated to determine what Columns and always columns must be examined.
You start by entering what course you teach! That can be in eight columns, "
K, L, M, N O, P, Q, or R"

That is entered via an InputBox. You enter the Column into the box.
You then enter the course you want to schedule into another InputBox. I
teach four different Literature and English courses.

The system then analysizes each student (based on columns, not
cells) to determine if they are eligible and if the guidance department feels
that can get a passing grade in that course.

This application was on an IBM AS 400 and was used in over 4,000
districts in the U.S. The Microsoft Consulting Team (none of them have ever
programmed or taught in high school) have sold over 1,000 school districts
that this can be done using Excel. The smaller high schools (recommended by
the Gate's Foundation) do not have the financial resources to hire
experienced programmers so teachers like myself have to figure out how to do
it.

In all honesty my Macros are so messed up with patching from users
groups, that I am embarrassed to display what I have.

However it is all about filtering columns, not cells.

Enter a column, filter the records requested.

Enter another criteria, filter the records that match that criteria from the
lines (records) selected above.

Is the course an honors or advanced course?

Filter another unknown column and so on!

If any selected column has a blank, print the students name (there should
not be any blanks, but because we have to hire teachers if we need them, we
ignore the blanks and find out why information is missing on the student.

We than have to do a mail merge to contact the parent to see if they want
their child in the class.

One experienced "C" programmer has indicated via the users group that his
code is over 3,000. lines in a Macro, however I am pretty sure he is in a
very, very large high school.

Thanks for your concern and helping!

Granny