Unable to get the match properties of the worksheet function Class

Question

I am trying to create a user form with "Search" function, however the "search" function is not working due to the error 1004.

The error Run Time Error 1004- Unable to get the match properties of the worksheet function Class.

After lots of reading from internet, some suggest to change the worksheetfucntion.match to application.match. However, the error still exists.

I attached piece of the code below and the parts where happen error is:

iColumn = WorksheetFunction.Match(sColumn, shDatabase.Range("A1:O1"), 0)

I have been stuck with this error and couldn't find solution for it.

Application.ScreenUpdating = False

Dim shDatabase As Worksheet 'Database Sheet
Dim shSearchData As Worksheet 'SearchData Sheet

Dim iColumn As Integer 'To hold the selected column database sheet
Dim iDatabaseRow As Long 'To store last non-blank row number available in database sheet
Dim iSearchRow As Long 'To hold the last non-row number available in SearchData Sheet

Dim sColumn As String 'To store the column selection
Dim sValue As String 'To hold the search text value


Set shDatabase = ThisWorkbook.Sheets("Database")
Set shSearchData = ThisWorkbook.Sheets("SearchData")

iDatabaseRow = ThisWorkbook.Sheets("Database").Range("A" & Application.Rows.Count).End(xlUp).Row

sColumn = frmForm.cmbSearchColumn.Value

sValue = frmForm.txtSearch.Value

' Where the error happens
If Not IsError( iColumn = WorksheetFunction.Match(sColumn, 
shDatabase.Range("A1:O1"), 0) ) Then
 ThisWorkbook.Sheets("Database").AutoFilterMode = False
Else:
 ThisWorkbook.Sheets("SearchData").AutoFilterMode = False
End If

ThisWorkbook.Sheets("SearchData").Cells.Clear

'Remove filter from databse worksheet
If shDatabase.FilterMode = True Then
    shDatabase.AutoFilterMode = False
End If

'Apply filter on Database worksheet
If frmForm.cmbSearchColumn.Value = "JPE PE" Then
    shDatabase.Range("A1:O" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue

Else
    shDatabase.Range("A1:O" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"
End If

If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then
    'Code to remove the previous data from SearchData Worksheet
    shSearchData.Cells.Clear
    shDatabase.AutoFilter.Range.Copy shSearchData.Range("A1")
    Application.CutCopyMode = False
    iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
    frmForm.lstDatabase2.ColumnCount = 15
    frmForm.lstDatabase2.ColumnWidths = "20,40,55,55,55,55,55,55,55,55,55,45,45,50,85"

    If iSearchRow > 1 Then

        frmForm.lstDatabase2.RowSource = "SearchData!A2:O" & iSearchRow

        MsgBox "Record found."

    End If

Else

    MsgBox "No record Found."

End If

shDatabase.AutoFilterMode = False
Application.ScreenUpdating = True

End Sub

Does this answer your question? [Excel VBA: Can't get a match, error "Unable to get the Match property of the WorksheetFunction class"](https://stackoverflow.com/questions/17751443/excel-vba-cant-get-a-match-error-unable-to-get-the-match-property-of-the-wor) — BigBen, Apr 28 '20 at 01:46
@BigBen I am new in VBA coding, do you mind if I send you the file and you take a look from it? Because I couldn't implement the "iserror" in my code. Really appreciate for your reply! — yeuanxiong, Apr 28 '20 at 02:04
Can you [edit] your question with how you tried to implement it? — BigBen, Apr 28 '20 at 02:10
Hi, I have edited my question, please take a look and tell me if there any problem. — yeuanxiong, Apr 28 '20 at 02:41
The line of code below will give you an error: 'If Not IsError( iColumn = WorksheetFunction.Match(sColumn, shDatabase.Range("A1:O1"), 0) ) Then' You cannot assign to a variable and test IsErrorin the same line of code — ChipsLetten, May 09 '21 at 12:21

score 1 · Answer 1 · answered Apr 28 '20 at 04:06

1

Here's an outline of the typical approach:

'...
Dim m
'...
m = Application.Match(sColumn, shDatabase.Range("A1:O1"), 0)
If Not IsError(m) Then 
    'got a match - use m here
else
    'no match found
End If

answered Apr 28 '20 at 04:06

Tim Williams

154,628
8
97
125

The code I have above doesn't seems able to implement the "IsError" because I have recall the matched value in filter mode. – yeuanxiong Apr 28 '20 at 04:34

Unable to get the match properties of the worksheet function Class

1 Answers1