Code states Match argument not optional

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

Unfortunately the link you posted doesn't work.

The first few times I tried the link, it wasn't working. I tried again and I was able to download your file. My apologies but I am a little confused by your explanation. Could you please explain using a few examples from your data in the file and stating what the result would be in column C for each example?

Try:

Sub matchVals()
    Application.ScreenUpdating = False
    Dim bottomB As Long
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    Dim foundVal As Range
    For Each rng In Range("B2:B" & bottomB)
        If rng <> "" Then
            Set foundVal = Range("H:H").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not foundVal Is Nothing Then
                Range("C" & rng.Row) = foundVal.Offset(0, 1)
            End If
        Else
            Set foundVal = Range("H:H").Find(rng.Offset(0, -1), LookIn:=xlValues, lookat:=xlWhole)
            If Not foundVal Is Nothing Then
                Range("C" & rng.Row) = foundVal.Offset(0, 1)
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Here you go.

Sub findmatch()

    Dim f As Long, x As Long, sh As Worksheet
    
    Set sh = ActiveSheet
    f = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
        If f < 2 Then f = 2
    
        For x = 2 To f
            With Application
                If Not IsError(.Match(sh.Range("B" & x).Value, sh.Range("GRADE"), 0)) Then
                sh.Range("C" & x) = .Index(sh.Range("SCORE"), .Match(sh.Range("B" & x), sh.Range("GRADE"), 0))
                Else: sh.Range("C" & x) = .Index(sh.Range("SCORE"), .Match(sh.Range("A" & x), sh.Range("GRADE"), 0))
                End If
            End With
        Next
End Sub

You hadn't mentioned that column B could have something "non-Grade". If you are still interested, this should work.

Sub matchVals()
    Application.ScreenUpdating = False
    Dim bottomB As Long
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    Dim foundVal As Range
    For Each rng In Range("B2:B" & bottomB)
        If rng <> "" Then
            Set foundVal = Range("H:H").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
            If Not foundVal Is Nothing Then
                Range("C" & rng.Row) = foundVal.Offset(0, 1)
            Else
                Set foundVal = Range("H:H").Find(rng.Offset(0, -1), LookIn:=xlValues, lookat:=xlWhole)
                If Not foundVal Is Nothing Then
                    Range("C" & rng.Row) = foundVal.Offset(0, 1)
                End If
            End If
        Else
            Set foundVal = Range("H:H").Find(rng.Offset(0, -1), LookIn:=xlValues, lookat:=xlWhole)
            If Not foundVal Is Nothing Then
                Range("C" & rng.Row) = foundVal.Offset(0, 1)
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Hi !

In case of not matching can't work with WorksheetFunction. Never use it but just Application
(example : Application.Match) and check result as error when can't match …

No problem. I'm glad that it all worked out in the end.