unable to get match property of worksheetfunction class

[girish2103]

i am getting error plz help me out to solve its


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

[Paul_Hossler]

It would be helpful if

1. You told us what the error was

2. What line seems to cause the error

3. Attached a small workbook with your macro and enough data to demonstrate the error



In your post, you can use the [#] icon to insert CODE tags and paste your macro between then. The software will format and set it off

I edited your post this time

[p45cal]

It's probably because there was no match to be found.
Some pointers:
Take out .WorksheetFunction from
iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:I1"), 0)
If there's no match, then iColumn will contain an error (instead of the code throwing an error and stopping execution), so elsewhere in the code test for that error (or its absence):

If iserror(iColumn) then
'what to do here if there's no match
else
'what to do here if a match has been found
End if