ค้นหาแล้ว Error
code search
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, shSearchData.Range("A1:Q1"), 0) ****Error*****
'Remove filter from Database worksheet
If shDatabase.FilterMode = True Then
shDatabase.AutoFilterMode = False
End If
'Apply filter on Database worksheet
If frmForm.cmbSearchColumn.Value = "Company name" Then
shDatabase.Range("A1:Q" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="sValue"
Else
shDatabase.Range("A1:Q1" & 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 = 16
frmForm.lstDatabase.ColumnWidths = "30, 60, 75, 40, 60, 45, 55, 70, 70, 30, 30, 30, 30, 30, 30, 30"
If iSearchRow > 1 Then
frmForm.lstDatabase.RowSource = "SearchData!A2:Q" & iSearchRow
MsgBox "Records found."
End If
Else
MsgBox "No record found."
End If
shDatabase.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub
unable to get the match property of the worksheetfunction class
code search
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, shSearchData.Range("A1:Q1"), 0)****Error*****'Remove filter from Database worksheet
If shDatabase.FilterMode = True Then
shDatabase.AutoFilterMode = False
End If
'Apply filter on Database worksheet
If frmForm.cmbSearchColumn.Value = "Company name" Then
shDatabase.Range("A1:Q" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="sValue"
Else
shDatabase.Range("A1:Q1" & 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 = 16
frmForm.lstDatabase.ColumnWidths = "30, 60, 75, 40, 60, 45, 55, 70, 70, 30, 30, 30, 30, 30, 30, 30"
If iSearchRow > 1 Then
frmForm.lstDatabase.RowSource = "SearchData!A2:Q" & iSearchRow
MsgBox "Records found."
End If
Else
MsgBox "No record found."
End If
shDatabase.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub