Multiple Criteria From Differing Rows

Re: Multiple Criteria From Differing Rows

My code ran fine in your sample. See attached zip file including code.

When you run it in the live file does the code stop immediately or does it process some of the records.

I’ll be out for a while, but check in later today.

Bill

Re: Multiple Criteria From Differing Rows

Just a note. I posted a formula for the original question - I haven't looked at anything since then.

Re: Multiple Criteria From Differing Rows

From what you describe you are running the code correctly.

I can not get my code to fail with the sample data you sent.

A few questions.

Were you able to run the code in the sample zip file I posted?

When you ran the code in your live file and got the error message 9 did you look at the data sheet to see if any 3’s were posted in column “K”?

In the live file are your 1’s in column “I” and 2’s in column ”J” like the sample data?

The error code 9 indicates that either the CourseII range or the Student has not been properly setup before the Find statement is executed.

To see if CourseII range is setup properly lets put two test lines of code in the Find Students taking CourseII section of the code. This section is documented with the comment “'Find Students taking CourseII”. Just add the two lines as indicated.

'Find Students taking CourseII
FirstCourseRow = i
For i = i + 1 To 65536
    If IsEmpty(Cells(i, 10)) Then Exit For
Next i
Set CourseII = Range(Cells(FirstCourseRow, 8), Cells(i - 1, 8))


CourseII.Select    	 'Add this test code here
End                		 'Add this test code here


'Find students taking both courses

Run the code as you have been. The Students for the first CourseII in your data should be selected. Go to your data sheet and scroll down to see in fact if they are highlighted. Do not click on any cells before scrolling.

Bill

Re: Multiple Criteria From Differing Rows

Hi Patty,

Where did the original data come from? Was it exported from a database or has it been manually maintained by a person?

Brad

Re: Multiple Criteria From Differing Rows

Patty,

Is this the unique list of the courses?

Accounting I
Accounting II
Adult Living
Arch Drafting 1
Arch Drafting 2
Auto Serv Occ 1
Auto Serv Occ 2
Auto Theory Mnt
Bus Comp Apps I
CAD
Child Development
Child/Day Care
Computer Graphics
Const/Build I
Const/Build II
CWT - Classroom
CWT - Coop I
CWT - Coop II
Desktop Publish
Drafting 1
Drafting/CAD
Fashion Opp I
Fashion Opp II
Foods & Nutri 1
Foods & Nutri 2
Formatting I
Formatting II
Graphic Occ 1
Graphic Occ 2
Graphics 1
Int Bldg Trades
Marketing
Materials/Proc
Metals
Or Fam & Cons Sci
Orient Ind Occ
Pre-Voc Coop
Pre-Voc Interrelated
Pre-Voc Skills
Retail Marketing
Woods II

Brad.

Re: Multiple Criteria From Differing Rows

Patty,

I too got the same error 9 message so it isn't just your machine.

I'll assume that the database this came from didn't have fields for tracking if the course was a 2nd part of a previous course. If it had anything like that then I would be exporting that data with it.

I'll also assume you are only processing the data to look at it and do not need to maintain this spreadsheet.

This would be my approach. Set up another worksheet with just the courses on it. Column A should have the first course in it, Column B should have the 2nd part of that course in it. You will probably end up with a list something like this:

COLUMN A		COLUMN B
Accounting I		Accounting II
Arch Drafting 1		Arch Drafting 2
Auto Serv Occ 1		Auto Serv Occ 2
Const/Build I		Const/Build II
CWT - Coop I		CWT - Coop II
Fashion Opp I		Fashion Opp II
Foods & Nutri 1		Foods & Nutri 2
Formatting I		Formatting II
Graphic Occ 1		Graphic Occ 2

If students were not allowed to commense a Part II of a course this should be a lot easier, but I will assume they can for now.

Loop through each student
	
	If student has more than one entry in table
		
		If the course has a second part and student has it in table
		
			Add a 3 to column K
			
		End If
		
	End If
	
End looping through students

There are any number of ways you could do this, I guess it just depends on what you are going to be doing with the data later on.

Brad

Re: Multiple Criteria From Differing Rows

Patty,
I had some other questions on my prior post.

Were you able to run the code in the sample zip file I posted?

When you ran the code in your live file and got the error message 9 did you look at the data sheet to see if any 3’s were posted in column “K”?

Re: Multiple Criteria From Differing Rows

Hi Bill,

I know when I ran the code and got the error 9, none of the cells in column K had 3's in them.

I haven't looked into the code in great detail so I'm not sure why this occured.

Brad

Re: Multiple Criteria From Differing Rows

Brad,
Thanks for the input. I can’t get the code to fail. What data are you running it against, Patty’s sample file?

I wonder if it’s an xl version problem. I notice you and Patty are running xl2003. I have xl2000 on Windows 98, the old dog.

I understand the line of code that fails is

Set BothCoursesTrue = CourseII.Find(Student, LookIn:=xlValue, lookat:=xlWhole)

Can you put an error routine in and capture the row number being processed when it fails

On Error GoTo Errhandler
    Set BothCoursesTrue = CourseII.Find(Student, LookIn:=xlValue, lookat:=xlWhole)
‘code
‘code
GoTo StartNextCourse
Errhandler:
MsgBox "Processing Row: " & Student.Row


End Sub

Bill

Re: Multiple Criteria From Differing Rows

Hi Bill,

Yes that is the line that the code gets stuck on:

Set BothCoursesTrue = CourseII.Find(Student, LookIn:=xlValue, lookat:=xlWhole)

It is possible it is to do with the version of excel being used.

I put the error handler in and it returned:
"Processing Row 2"

I am using the latest file attachment that was posted.

Hope that helps some.

Brad

Re: Multiple Criteria From Differing Rows

Brad,

Line 2 is the first line of data.
So its failing on the very first attempt to find a match in CourseII. But Patty ran a test for me and the correct courses were being placed in the range variable CourseII.

I’m running the same code and same data. Patty says she could not run the test file I sent her, but it ran OK on my system. I’ll have to sleep on this one and look some more tomorrow.

Thanks for your help.

Re: Multiple Criteria From Differing Rows

Hi,

[vba]Sub kTest()
Dim a, w(), i As Long, dic As Object, j As Long, x, y, z
Set dic = CreateObject("scripting.dictionary")
a = [a1].CurrentRegion.Resize(, 11)
For i = 2 To UBound(a, 1)
If Not IsEmpty(a(i, 8)) And Not dic.exists(a(i, 8)) Then
ReDim w(1 To 2): w(1) = a(i, 9): w(2) = a(i, 10)
dic.Add a(i, 8), w
Else
w = dic(a(i, 8))
w(1) = dic(a(i, 8))(1) + a(i, 9): w(2) = dic(a(i, 8))(2) + a(i, 10)
dic(a(i, 8)) = w
End If
Next: y = dic.keys: z = dic.items: Set dic = Nothing
For i = 2 To UBound(a, 1)
For j = 0 To UBound(y)
If a(i, = y(j) Then
x = z(j)(1) + z(j)(2)
If x = 3 Then: Cells(i, 11) = 3: Exit For
End If
Next
Next
End Sub[/vba]

HTH