unable to get match property of worksheetfunction class

[Girish2103]

Here is the the code plz check

Sub SearchData()

    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("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
   
    iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:I1"), 0)
   
   
   
       
    'Remove filter from Database worksheet
   
    If shDatabase.FilterMode = True Then
   
        shDatabase.AutoFilterMode = False
   
    End If

    'Apply filter on Database worksheet
   
    If frmForm.cmbSearchColumn.Value = "House No" Then
   
        shDatabase.Range("A1:I" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue
   
    Else
   
        shDatabase.Range("A1:I" & 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.lstDatabase.ColumnCount = 10
       
        frmForm.lstDatabase.ColumnWidths = "30,60,75,75,75,75,75,75,75,70,70"
       
        If iSearchRow > 1 Then
       
            frmForm.lstDatabase.RowSource = "SearchData!A2:I" & iSearchRow
           
            MsgBox "Records found."
       
        End If
       
       
    Else
   
       MsgBox "No record found."
   
    End If

    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True


End Sub

 

[RoryA]

The error means that the value you were looking for wasn't found. What type of data is it - numeric?

 

[Girish2103]

yes

 

[RoryA]

Then try:

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

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: unable to get match property of worksheetfunction class
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.