Comparing against table to return value

[iMAN2]

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|
+-------------------------------------------------------------------+

 

[Joel]

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]

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

 

[Joel]

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]

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|
+-------------------------------------------------------------------+

 

[Joel]

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]

Hi,
In regards to the 'check' only need that when there isnt a value to
compare against, all it is a last measure (not very likely to get
'check') and if i do then using condional formatting itll be easy to
spot. But show 'check' when no value to compare against.

Your original code was fine, you shouldnt really have to change
anything except for the updating. Maybe something like, when u run the
macro, it deletes the old file and then produces the new export one
again.

Also as there are two enter sheets, half-yearly and trials, i think
having two macros would be easier (to code and itll give more
flexibility to run).

Thankyou

 

[iMAN2]

_Summary_of_things_needing_tweaking:_
When macro is run second time around, it should update the original
export instead of creatiing it as a new sheet.

There are two enter sheets so coding needs to by applied to those two
sheets, note second sheet has a few extra columns of data.

If there is no value to compare against for the marks then in the
export read 'check'.

The table starts from row 3 (the headings) whilst data entries (name
etc) run from row 4 to row 299 (both enter sheets)

If subject has word 'accelerated' in it, then export the original mark
without comparing against table