Multiple Criteria From Differing Rows

Re: Multiple Criteria From Differing Rows

Quote from psindelar

Bill,
That worked beautifully! Now, what is the best way for me to learn VBA? Book or class?

Hi Patty. If you can find a course on VBA then that will wet your appetite and get you started. But I'd have to say a good book will do equally as well as any course. Then the forums will really start to advance your progress as you've got access to answers to problems that are specific to your needs.

So to answer your question...all 3: Books and forums...and if you can find one with substance, a course but I wouldn't be too worried about doing a course.

Brad

Re: Multiple Criteria From Differing Rows

Bill,

Replace

[vba]If a(i, = z(j)(1) And b(i, 2) = z(j)(2) Then[/vba]

with

[vba]If a(i, = z(j)(2) And b(i, 2) = z(j)(1) Then[/vba]

I think your find method is better here because of partial match of course name.

Re: Multiple Criteria From Differing Rows

Quote from Bill Rockenbach

Hi all,

The data is setup in cadenced order of subsets of courses (1 and 2):

course1a 1
course1b 2
course2a 1
course2b 2
course3a 1
course3b 2
.
.
.

Display More

change

.Pattern = "I+$"

to

.Pattern = "\D+$"

Re: Multiple Criteria From Differing Rows

jindon,
Still get error 5 Invalid Processing Call

Krishnakumar,
Your result matched mine except
1. No 3’s for the last course set in the test data, Marketing/Retail Marketing
2. You found a match in Bus Comp Apps I and II that I did not. You are keying on column A. I keyed on the break of 1 and 2 in columns I and J.

Patty,

I think there is a data problem as Bus Comp Apps I is labeled both with 1’s and 2’s in the test data you sent (rows 287 through 315). Is your live data OK.

Bill

Re: Multiple Criteria From Differing Rows

I have no idea about the error that you are getting.

I give up this, coz I can't see the actual data.

Re: Multiple Criteria From Differing Rows

Patty,

If the course numbers are suppose to be uniquely labeled with either a 1 or a 2 then you have some data integrity issues with the test data you sent. If that is the case then this program will spot courses not labeled properly.

Sub DataIntegrityTest()
Dim CurrentCourse As String
Dim TestNumber
Dim c As Range


CurrentCourse = Range("A2")
TestNumber = 1
For Each c In Range("A2", Range("A65536").End(xlUp))
    If c <> CurrentCourse Then
        CurrentCourse = c
        TestNumber = IIf(TestNumber = 1, 2, 1)
    End If
    If c.Offset(0, 8) + c.Offset(0, 9) <> TestNumber Then
        c.Select
        MsgBox "Possible Data error at row: " & c.Row
    End If
Next c

Bill

Re: Multiple Criteria From Differing Rows

All the people that have contributed to this thread have done a fantastic job.

I do think that the database that holds this information needs to be looked at by the database administrator to possibly have another field added like "parent course".

This would make the data a lot easier to manipulate within a spreadsheet and also the database holding the info.

For example:

[B]Dept	Course	Course Name	[COLOR="SeaGreen"]Parent Course[/COLOR]	Sec	Course Length	Credits	Student ID	S1	S2	FY[/B]
BUS	3502	Formatting II	[COLOR="SeaGreen"]Formatting I[/COLOR]	1	Semester	5	270144		1

Or something along those lines.

Brad.