Col H contains the range of possible "Grades". Col I contains the range of equivalent weighted "Scores"
Col A contains Expected grades. Col B contains Actual grades (where known).
Following Code should work as follows:
If Col B does not match a "Grade" in Col H, then Col C is the "Index" of Col I that "Match" Col A against "Grade" in Col H
Otherwise Col C is the Index/Match of the Grade in Col B
But it throws a "Compile error" that "Match argument is not optional."
Sub findmatch()
With ActiveSheet
f = Range("A" & Rows.Count).End(xlUp).Row
If f < 2 Then f = 2
For x = 2 To f
If Error(Application.WorksheetFunction.Index(Range("SCORE"), Application.WorksheetFunction.Match("B" & f), Range("GRADE"), 0), 1) Then
Range("C" & f) = Application.WorksheetFunction.Index(Range("SCORE"), Application.WorksheetFunction.Match("A" & f), Range("GRADE"), 0)
Else: Range("C" & f) = Application.WorksheetFunction.Index(Range("SCORE"), Application.WorksheetFunction.Match("B" & f), Range("GRADE", 0))
End If
End Sub
I know I could use the "formula" approach, but in this case I need a specific VBA solution.
Any pointers, solutions or alternative welcome as ever.
Ochimus
Bookmarks