How do I clear this VBA Error when using Match Function-CodePudding

I have written a VBA script that adds the information selected in a userform to a specific row in a spreadsheet.

Now I'm trying to create a button that allows for users to edit the userform. I initially used the code


Dim targetrow As Integer

'Case select being the drop-down box where users select the case number

'Tracker is the spreadsheet and Case_ID is the column that contains the case ID

targetrow = Application.WorksheetFunction.Match(Case_Select, Sheets("Tracker").Range("Case_ID"), 0)

MsgBox targetrow

'Just want to test and display the number

End Sub

But I got an error,

"Unable to get the Match property of the WorksheetFunction class"

I did some research and everyone was saying to remove the worksheetfunction and just use match, which I did as shown here:

Private Sub CommandButton1_Click()

Dim targetrow As Integer

Dim case_id As Integer


targetrow = Application.Match(Case_Select, Sheets("Tracker").Range("Case_ID"), 0)


End Sub

However, now I'm getting a

"Run-time error 13": Type Mismatch"

Not sure what is causing this or how to fix it. Please help!

CodePudding user response：

Use a Variant for targetrow, otherwise it can't handle the error value returned when Application.Match doesn't make a match.

Private Sub CommandButton1_Click()

    Dim targetrow As Variant
    Dim case_id As Integer


    targetrow = Application.Match(Case_Select, Sheets("Tracker").Range("Case_ID"), 0)
    If Not IsError(targetrow) Then
        'got a match, and `targetrow` is the matched row
        Debug.Print "Matched on row# " & targetrow
    Else
        Debug.Print "No match" 
    End If
End Sub