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

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.

--
Regards,
Tom Ogilvy




"GrandMaMa" wrote:

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

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!

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy




"GrandMaMa" wrote:

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

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.



--
Regards,
Tom Ogilvy


"GrandMaMa" wrote:

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!

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy




"GrandMaMa" wrote:

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

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

"Tom Ogilvy" wrote:

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.



--
Regards,
Tom Ogilvy


"GrandMaMa" wrote:

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!

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy




"GrandMaMa" wrote:

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

Add an "S"

Set NWB = WorkbookS.add



GrandMaMa wrote:

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

"Tom Ogilvy" wrote:

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.



--
Regards,
Tom Ogilvy


"GrandMaMa" wrote:

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!

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy




"GrandMaMa" wrote:

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


--

Dave Peterson