"run time error 1004 method match property of the worksheetfunction class

9kitti · Oct 28th 2021

I'm facing the problem "run time error 1004 method match property of the worksheetfunction class" trying to find a solution. But I don't understand very much about VBA. Please suggest me how to fix it. thank you

Code

Sub SearchData_recives()

    Application.ScreenUpdating = False
    
    Dim shDatabase As Worksheet ' Database sheet
    Dim shSearchData As Worksheet 'SearchData sheet
    
    Dim iColumn As Integer 'To hold the selected column number in Database sheet
    Dim iDatabaseRow As Long 'To store the last non-blank row number available in Database sheet
    Dim iSearchRow As Long 'To hold the last non-blank row number available in SearachData sheet
    
    Dim sColumn As String 'To store the column selection
    Dim sValue As String 'To hold the search text value
    
    Set shDatabase = ThisWorkbook.Sheets("DatabaseOUT")
    Set shSearchData = ThisWorkbook.Sheets("SearchDataOUT")
    
    iDatabaseRow = ThisWorkbook.Sheets("DatabaseOUT").Range("A" & Application.Rows.Count).End(xlUp).Row
    
    sColumn = frmRecives.cmbrecivesSearchColumn.value
    
    sValue = frmRecives.recivestxtSearch.value
    
    iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:L1"), 0)
    
    'Remove filter from Database worksheet
    
    If shDatabase.FilterMode Then
    shDatabase.AutoFilterMode = False
    'shDatabase.ShowAllData
    shDatabase.ListObjects("Table3").AutoFilter.ShowAllData
End If

    'Apply filter on Database worksheet
    
    If frmRecives.cmbrecivesSearchColumn.value = "No.Recives" Then

        shDatabase.Range("A1:L" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue
    
    Else
    
        shDatabase.Range("A1:L" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"
    
    End If
    
    If Application.WorksheetFunction.Subtotal(3, shDatabase.Columns(iColumn)) >= 2 Then
    
        'Code to remove the previous data from SearchData worksheet
        
        shSearchData.Cells.Clear
        
        shDatabase.UsedRange.Copy shSearchData.Range("A1")
        
        Application.CutCopyMode = False
        
        iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
        
        frmRecives.recDatabase.ColumnCount = 12
        
        frmRecives.recDatabase.ColumnWidths = "40,70,75,75,75,80,70,70,70,350,70,70"
        
        If iSearchRow > 1 Then
        
            frmRecives.recDatabase.RowSource = "SearchDataOUT!A2:L" & iSearchRow
            
            MsgBox "Found!!!"
        
        End If
        
    Else
    
       MsgBox "Not Found!!!"
    
    End If

    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True

End Sub

Display More

**royUK** · Oct 28th 2021

Explain what you are doing and what these variables are populated with

Code

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

An example workbook might help.

9kitti · Oct 28th 2021

Quote from royUK
Explain what you are doing and what these variables are populated with
Code
 Dim sColumn As String 'To store the column selection
    Dim sValue As String 'To hold the search text value
An example workbook might help.

this workbook, help me pls.

**royUK** · Oct 28th 2021

Code

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

iColumn is declared as an integer, but the formula will not return an integer.

I think you are looking for a specific column in the header row. If you are then use the FInd method which will return a cell and from that get the column number.

Also, put the code into the UserForm not a Module.

9kitti · Oct 28th 2021

Quote from royUK
Code
 iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:L1"), 0)
iColumn is declared as an integer, but the formula will not return an integer.

I think you are looking for a specific column in the header row. If you are then use the FInd method which will return a cell and from that get the column number.

Also, put the code into the UserForm not a Module.

I'm not very knowledgeable in VBA. would be greatly appreciated If you can help me fix it in a submission that I don't know. thank you very much

**royUK** · Oct 28th 2021

Don't keep using Code Tags for your replies unless you are posting code.

What is the code looking for?

"run time error 1004 method match property of the worksheetfunction class

Participate now!

Share

Tags