Hi guyz,

I'll just give a quick run down of what i really need. Sample data is
attached.

Sheet 1 - Enter Marks
Sheet 2 - Moderation Comparison
Sheet 3 - Moderated marks (output)

Data is inputted into sheet 1. The marks for each respective subject
need to be compared against the table in sheet 2. This new mark is then
to be outputted in the respective columns in sheet 3.

Specific things needed: (text colour green in sample sheet for easier
reference)

If a subject contains the word 'accelerant' then for that name entry a
comparison shouldn't be made and the original mark should be kept as the
output mark. E.g. Chemistry

If there are mutliples of a subject for 1 entry, e.g. 2 physics then it
should return a pop-up error (stating for which name entry this
occured). If this is not possible, then in sheet 3 for the mark it
should read 'check'. An example of this is in sheet 3 for the last entry
in the sample data.

If for the comparison there is no comparison mark in sheet 2 to compare
the sheet 1 mark against, then the output mark in sheet 3 for that entry
should read 'check'.

Crosspost at: 'VBA Express Forum'
(http://www.vbaexpress.com/forum/showthread.php?t=26981)

Thankyou guyz for your help. :)


+-------------------------------------------------------------------+
|Filename: Moderator (Moderation Sample Data).xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=149|
+-------------------------------------------------------------------+

--
iMAN2
------------------------------------------------------------------------
iMAN2's Profile: http://www.thecodecage.com/forumz/member.php?userid=371
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=102545

Try this

Sub CorrectMarks()


Set Sourcesht = Sheets("Half-Yearly - ENTER MARKS")
Set LookupSht = Sheets("Moderation Comaprison")

'copy enter marks to new worksheet
Sourcesht.Copy after:=Sheets(Sheets.Count)
Set ModifiedSht = ActiveSheet
SourceShtName = Sourcesht.Name
ModifiedSht.Name = Replace(SourceShtName, "ENTER", "Moderated")

With ModifiedSht
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
LastCol = .Cells(3, Columns.Count).End(xlToLeft).Column

For RowCount = 4 To Lastrow
If .Range("A" & RowCount) < "" Then
For ColCount = 7 To LastCol Step 2
Subject = .Cells(RowCount, ColCount)
If Subject < "" Then
'check for duplicates
Set CheckRange = .Range(Range("G" & RowCount), _
Cells(RowCount, LastCol))
SubjectCount = WorksheetFunction.CountIf( _
CheckRange, Subject)
If SubjectCount 1 Then
.Cells(RowCount, ColCount).Offset(0, 1) = "Check"
.Cells(RowCount, ColCount).Font.ColorIndex = 5
.Cells(RowCount, ColCount).Offset(0, 1).Font.ColorIndex = 5

Else
'skip subjects with accelerent
If InStr(UCase(Subject), "ACCELERANT") = 0 Then
OldGrade = .Cells(RowCount, ColCount).Offset(0, 1)
NewGrade = ""
With LookupSht
Set GradeRow = .Columns("A").Find(what:=OldGrade, _
LookIn:=xlValues, lookat:=xlWhole)
If GradeRow Is Nothing Then
MsgBox ("Error in cell : " & _
.Cells(RowCount, ColCount).Offset(0, 1).Address)
Else
Set SubjectCol = .Rows("1").Find(what:=Subject, _
LookIn:=xlValues, lookat:=xlWhole)
If SubjectCol Is Nothing Then
MsgBox ("Error in cell : " & _
.Cells(RowCount, ColCount).Address)
Else
NewGrade = .Cells(GradeRow.Row,
SubjectCol.Column)
End If
End If
End With
If NewGrade < "" Then
.Cells(RowCount, ColCount).Offset(0, 1) = NewGrade
.Cells(RowCount, ColCount).Offset(0,
1).Font.ColorIndex = 3
End If
End If

End If

End If

Next ColCount
End If
Next RowCount
End With

End Sub


"iMAN2" wrote:


Hi guyz,

I'll just give a quick run down of what i really need. Sample data is
attached.

Sheet 1 - Enter Marks
Sheet 2 - Moderation Comparison
Sheet 3 - Moderated marks (output)

Data is inputted into sheet 1. The marks for each respective subject
need to be compared against the table in sheet 2. This new mark is then
to be outputted in the respective columns in sheet 3.

Specific things needed: (text colour green in sample sheet for easier
reference)

If a subject contains the word 'accelerant' then for that name entry a
comparison shouldn't be made and the original mark should be kept as the
output mark. E.g. Chemistry

If there are mutliples of a subject for 1 entry, e.g. 2 physics then it
should return a pop-up error (stating for which name entry this
occured). If this is not possible, then in sheet 3 for the mark it
should read 'check'. An example of this is in sheet 3 for the last entry
in the sample data.

If for the comparison there is no comparison mark in sheet 2 to compare
the sheet 1 mark against, then the output mark in sheet 3 for that entry
should read 'check'.

Crosspost at: 'VBA Express Forum'
(http://www.vbaexpress.com/forum/showthread.php?t=26981)

Thankyou guyz for your help. :)


+-------------------------------------------------------------------+
|Filename: Moderator (Moderation Sample Data).xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=149|
+-------------------------------------------------------------------+

--
iMAN2
------------------------------------------------------------------------
iMAN2's Profile: http://www.thecodecage.com/forumz/member.php?userid=371
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=102545

Thanks, works quite well.

Just one problem, if data is changed in the 'enter marks' sheet and
then re-exported the macro copies the 'enter marks' sheet without
moderating it and makes it into a new sheet call ' enter marks (2)'.

Is it possible so as when a re-export occurs, the data is updated in
the moderated marks sheet.

Thankyou


--
iMAN2
------------------------------------------------------------------------
iMAN2's Profile: http://www.thecodecage.com/forumz/member.php?userid=371
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=102545

Whe I saw your macro and the enter sheet was called "Half-Yearly" I thought
yo may have multiple sheets that need to be convertered. One for the 1st
half and a one for the 2nd half so I made the code flexible. Try these
changes to put the data into the same sheet. Because of all the formating in
the sheets I think it is better to delete the "Moderated" sheet and recreate
it rahter than to remove all the formating.

From:

Set Sourcesht = Sheets("Half-Yearly - ENTER MARKS")
Set LookupSht = Sheets("Moderation Comaprison")

'copy enter marks to new worksheet
Sourcesht.Copy after:=Sheets(Sheets.Count)
Set ModifiedSht = ActiveSheet
SourceShtName = Sourcesht.Name
ModifiedSht.Name = Replace(SourceShtName, "ENTER", "Moderated")


To:
Set Sourcesht = Sheets("Half-Yearly - ENTER MARKS")
Set LookupSht = Sheets("Moderation Comaprison")
Set ModifiedSht = Sheets("Half-Yearly - Moderated MARKS")

ModifiedSht.delete
'copy enter marks to new worksheet
Sourcesht.Copy after:=Sheets(Sheets.Count)
Set ModifiedSht = ActiveSheet
ModifiedSht.Name = "Half-Yearly - Moderated MARKS"


"iMAN2" wrote:


Thanks, works quite well.

Just one problem, if data is changed in the 'enter marks' sheet and
then re-exported the macro copies the 'enter marks' sheet without
moderating it and makes it into a new sheet call ' enter marks (2)'.

Is it possible so as when a re-export occurs, the data is updated in
the moderated marks sheet.

Thankyou


--
iMAN2
------------------------------------------------------------------------
iMAN2's Profile: http://www.thecodecage.com/forumz/member.php?userid=371
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=102545

Hi,
the attached file is exactly what the real one looks like. The inital
problem with the coding was that lets say i moderated it and it made the
'moderated - marks' sheet. However, if i changed same data in the
'enter' marks sheet and then re-exported, instead of updating the data
it would create a new sheet (which i don't want).

If a mark to compare against in the moderation table doesn't exist,
then only read 'check'.


Thanks


+-------------------------------------------------------------------+
|Filename: Moderate (New Format).xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=152|
+-------------------------------------------------------------------+

--
iMAN2
------------------------------------------------------------------------
iMAN2's Profile: http://www.thecodecage.com/forumz/member.php?userid=371
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=102545

I'm having an issue witth the types of changes you plan to make with the
Enter worksheet. The formating changes wil be a problem. Maybe I should
only copy the values of the cells from the enter to the new worksheet to
avoid problems. What happens if somebody adds a new row into the ENTER
worksheet?

I think your problem is if I put a check in the box and you perform the
check yo don't wnat to have to go back each time and verify all the checks.
Maybe I should only write checks into empty cells and not ones you modified
manually.

"iMAN2" wrote:


Hi,
the attached file is exactly what the real one looks like. The inital
problem with the coding was that lets say i moderated it and it made the
'moderated - marks' sheet. However, if i changed same data in the
'enter' marks sheet and then re-exported, instead of updating the data
it would create a new sheet (which i don't want).

If a mark to compare against in the moderation table doesn't exist,
then only read 'check'.


Thanks


+-------------------------------------------------------------------+
|Filename: Moderate (New Format).xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=152|
+-------------------------------------------------------------------+

--
iMAN2
------------------------------------------------------------------------
iMAN2's Profile: http://www.thecodecage.com/forumz/member.php?userid=371
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=102545

Hi,
the attached file is exactly what the real one looks like. The inital
problem with the coding was that lets say i moderated it and it made the
'moderated - marks' sheet. However, if i changed same data in the
'enter' marks sheet and then re-exported, instead of updating the data
it would create a new sheet (which i don't want).

If a mark to compare against in the moderation table doesn't exist,
then only read 'check'.


Thanks


+-------------------------------------------------------------------+
|Filename: Moderate (New Format).xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=152|
+-------------------------------------------------------------------+

--
iMAN2
------------------------------------------------------------------------
iMAN2's Profile: http://www.thecodecage.com/forumz/member.php?userid=371
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=102545