Run Macro 1 if Value X exists or Macro B if Value X exists twice

Hello there, me again! As always many thanks for all and any help I receive with this next problem.

This is quite a complicated one so please feel free to help in chunks rather than the whole at once...

From Row 9 onwards column A and C are checked.

Condition 1

If in column A and column C the same values exists in the next row down e.g. A9 = JONES C9 = Peter and A10 = JONES C10 = Peter then the data in column E is checked for those two rows.

In the two checked rows in Column E, data which contains the words Pre-int, Upper-int, Int, Elementary, Advanced or Beginner is then inserted into D5 on spreadsheets titled "Schedule A GE2" and "Schedule B GE2".

Data which contains the words Mid, High, Low or IELTS is inserted into D7 on spreadsheets titled "Schedule A GE2" and "Schedule B GE2".

If the data in D5 contains the words Upper-int or Advanced then "Schedule A GE2" is duplicated as a new spreadsheet in the same workbook with the spreadsheet name as the data from column A and column C from which the data was taken. If not then "Schedule B GE2" is duplicated and the same done instead.

Then in A5 on the new spreadsheet the data "Name: &Column A &Column C" is inserted.

The macro then continues checking down Column A and Column C for more data where there are two rows which are the same.

Condition 2

If the data in two rows is not the same i.e. A9 = JONES C9 = Peter and A10 = RUPERT C9 = Peter then the following is done instead.

In the two checked rows in Column E, data which contains the words Pre-int, Upper-int, Int, Elementary, Advanced or Beginner is then inserted into D5 on spreadsheets titled "Schedule A GE1" and "Schedule B GE1".

If the data in D5 contains the words Upper-int or Advanced then "Schedule A GE1" is duplicated as a new spreadsheet in the same workbook with the spreadsheet name as the data from column A and column C from which the data was taken. If not then "Schedule B GE1" is duplicated and the same done instead.

Then in A5 on the new spreadsheet the data "Name: &Column A &Column C" is inserted.

The macro then continues checking down Column A and Column C for more data where there are two rows which are not the same.


A huge thanks for everything helped with here, I realise I am asking a great deal.

Tom

Please Login or Register  to view this content.

So then rows 9 and 10 are identified as a pair. Once they have been processed presumably the processing will then continue from row 11?

Would this situation ever occur?

Please Login or Register  to view this content.

If it can occur, then would rows 9 and 10 be treated as a pair, and row 11 as a 'single' ?
Of course if it can't occur it doesn't matter, but I just need to confirm this

Thanks

Ok, thanks, I'll get back to you soon

An example workbook would probably help to answer a lot of these question if you could?

----

Condition 1
...
In the two checked rows in Column E, data which contains the words Pre-int, Upper-int, Int, Elementary, Advanced or Beginner is then inserted into D5 on spreadsheets titled "Schedule A GE2" and "Schedule B GE2".

Data which contains the words Mid, High, Low or IELTS is inserted into D7 on spreadsheets titled "Schedule A GE2" and "Schedule B GE2".

So I've check 2 rows, (say 9 & 10) and they match.
So now I look at Column E, looking for Pre-int, Upper-int, Int, Elementary, Advanced or Beginner.
Does data from the first row, (row9) go onto "Schedule A GE2", and the data from the second row, (row10), go onto "Schedule B GE2"?

That is what I think you want?

However, what then happens with condition 2?

Condition 2
...
In the two checked rows in Column E, data which contains the words Pre-int, Upper-int, Int, Elementary, Advanced or Beginner is then inserted into D5 on spreadsheets titled "Schedule A GE1" and "Schedule B GE1".

So now rows 9 & 10 are different, so do we still work on BOTH rows, or does the data from row9 go onto both "Schedule A GE1" AND "Schedule B GE1" ?

If we work on BOTH rows still, then what would happen if 9 & 10 were different, but 10 & 11 were the same?



Thanks

Hehe, sorry about this its quite hard to explain.

Only one of the sheets is used per person, basically high level students are on Schedule A and low levels are on Schedule B.

Some students have one class and others have two classes. Those that have two classes have two rows with their name in them hence the reason for checking for this.

So basically if there are two rows with the student's name (Column A) then their classes (Column E) are inserted into both of the timetables designed for when a student has two classes. So their Class Type 1 (Pre-int, Upper-int, Int, Elementary, Advanced) goes into D5 and their Class Type 2 (Mid, High, Low or IELTS) goes into D7.

Then based on their level (high levels being upper-int and advanced) they are assigned the A schedule and that timetable is generated for that person's name. If they have anything other than upper-int or advanced as their Class Type 1 then they are given the schedule B timetable.


If the data on the two rows is different you only use the data from one row and do the same essentially but there is no Class Type 2 so you only need to stick the data into D5. You then again select the correct timetable based on their level and create a new spreadsheet from that timetable with their name etc etc.

I have attached an example of how the data is laid out.

Many thanks

I realised a little late that I forgot to include the timetable samples in that fabricated workbook I uploaded. Here is the ammended copy.

Aha I understand, I think I was over complicating things

So the "Schedule x GE1" is for pupils with 1 class, whereas "Schedule x GE2" is for pupils with 2 classes.
Similarly "Schedule A GE#" is for pupils on the higher levels, whereas "Schedule B GE#" is for everyone else

I think I was getting confused because you were saying 'how' it should be done, rather than 'what' you wanted
(Well, it was enough to confuddle my grey cells anyway!)
I'll be back in a bit with something for you

EDIT
When the sheet is copied for a student's 'personalised' timetable, what should the sheet name be? If it needs to be the student name we might have name length problems IF a student had a long name. We can try it, and I can code in an input box that will ask for an alternative name if the student name is too long?

Please see the attached workbook, (the code is also repeated below for 'browsing' purposes).

I have implemented the 'InputBox' for names that are repeated, or too long, as I feel this is most flexible.
You may also notice that when I copy the timetable sheets I do not copy the 'Sheet', but rather insert a new blank sheet, and then copy the cells from one sheet to the other. This avoids the issue with repeatedly copying a worksheet within a workbook, (http://support.microsoft.com/kb/210684).
It then reapplies the 'Print_Area' that is defined on the TimeTable templates. I don't know if you are using the 'Print_Area' at all, but as it was defined on the original templates I have copied it to the timesheets. If you don't need it then just comment out the green text.

You will note that there are a few areas of code that are commented out. This is because during testing I was outputing the Timetables to a different workbook so that I could delete and restart them easily. I have left the code areas in below in case you find them useful.
To re-enable the outputing to a different workbook you need to comment the blue text, and uncomment the red text

Please let me know how you get on.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Slight problem the print area is quite important, each timetable does get printed. However with the current code it appears the the print area gets set from A1-D30 and then a new page at E1-F30, A31-D34 and E31-F34.

Instead the print area needs to be from A1-F34, i.e. the whole timetable all on one page.

Thanks

Tom

Edit: Realised I used the wrong terminology, it is basically the page breaks which need to be moved, the print area is correct.

Aha, ok I see now

Change:

Please Login or Register  to view this content.

To be:

Please Login or Register  to view this content.

Workbook attached with the alteration already made.