How to prevent the error of Unable to get the Match Property of the WorksheetFunction class error?

xiaowang · Feb 23rd 2020

Hi all attached is my code. I would like the program to go back to mainpage( a userform) once its unable to detect a match. Please do advice!

Code

Private Sub SubmitButton1_Click()

    If TextBox1.Text = "" Then
    Cancel = 1
    MsgBox "Please fill in the necessary"
    TextBox1.SetFocus
    Exit Sub
    End If
    If TextBox3.Text = "" Then
    Cancel = 1
    MsgBox "Please fill in the necessary"
    TextBox3.SetFocus
    Exit Sub
    End If
    If DTPicker1.Value = "" Then
    Cancel = 1
    MsgBox "Please fill in the necessary"
    DTPicker1.SetFocus
    Exit Sub
    End If
    If TextBox2.Text = "" Then
    Cancel = 1
    MsgBox "Please fill in the necessary"
    TextBox2.SetFocus
    Exit Sub
    End If
    If scanitemtextbox1.Text = "" Then
    Cancel = 1
    MsgBox "Please fill in the necessary"
    scanitemtextbox1.SetFocus
    Exit Sub
    End If
Worksheets("Equipment_Database").Activate
Range("T1").Value = scanitemtextbox1.Text 'user scans a barcode and outputs words into T1
Range("T2").Value = WorksheetFunction.Match(Range("T1").Value, Range("A:A"), 0) 'matches T1
Set random = Cells((Range("T2").Value), 1) 
Range("T3").Value = random.Address(RowAbsolute:=False) 'finds the address of T2
If Range(Range("T3").Value).Offset(0, 7).Value = 0 Then
MsgBox "Unable to withdraw due to insufficient equiupment/item is not registered "
Unload Me
Mainpage.Show
Else
Worksheets("Equipment_Database").Range(Range("T3").Value).Offset(, 3).Value = "No"
Worksheets("Equipment_Database").Range(Range("T3").Value).Offset(, 4).Value = Range(Range("T3").Value).Offset(, 4).Value + 1
Worksheets("Equipment_Database").Range(Range("T3").Value).Offset(, 7).Value = Range(Range("T3").Value).Offset(, 7).Value - 1

Worksheets("Withdrawal_data").Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 'Check for empty rows and sets sheet 2 as the relevant sheet to output to'
Cells(erow, 1) = TextBox1.Text 'output to whatever cell thats empty in column 1'
Cells(erow, 2) = TextBox3.Text 'output to whatever cell thats empty in column 2'
Cells(erow, 3) = DTPicker1.Value 'output to whatever cell thats empty in column 3'
Cells(erow, 4) = TextBox2.Text 'output to whatever cell thats empty in column 4'
Cells(erow, 6) = scanitemtextbox1.Text 'output to whatever cell thats empty in column 6'
Unload Me
Mainpage.Show

End Sub

Display More

**Carim** · Feb 24th 2020

Hello,

Below is a proposal to catch a Match Error

Code

Sub CatchMatch()
Dim res As Variant
On Error Resume Next
res = Application.WorksheetFunction.Match(Range("T1").Value, Range("A:A"), 0)
  If IsError(res) Then
       MsgBox "No match found"
  Else
       MsgBox "Match found in row " & res
  End If
On Error GoTo 0
End Sub

Display More

Hope this will help

**royUK** · Feb 24th 2020

I would use the Excel VBA Find Function instead of Match.

How to prevent the error of Unable to get the Match Property of the WorksheetFunction class error?

Participate now!

Share