Dear Champs.
By the following code I can search through the listbox column where there are text easily but when I choose column with numeric, it does not search. Gives back not found. But If I put search value with its format like 99,999.00 then it search. How can I improve my code where if I input a value 99999 it will search and list in my listbox.
I am too amateur in VBA, i do not know to write codes but I can interpret and tweet here and there. So bear with my shabby coding here below
YEAR, BFTE, BUNIT, BRATE, BAMT, FFTE, FUNIT, FRATE, FAMT, AFTE, AUNIT, ARATE, AAMT are all numeric columns but in list box appears as string and hence if I key a value with the format then it gives the search result. I want to input value without the format, just pure values.
Thank you in advance
By the following code I can search through the listbox column where there are text easily but when I choose column with numeric, it does not search. Gives back not found. But If I put search value with its format like 99,999.00 then it search. How can I improve my code where if I input a value 99999 it will search and list in my listbox.
I am too amateur in VBA, i do not know to write codes but I can interpret and tweet here and there. So bear with my shabby coding here below
VBA Code:
Sub SearchData()
Application.ScreenUpdating = False
Dim shDatabase As Worksheet ' Database sheet
Dim shSearchData As Worksheet 'SearchData sheet
Dim shPP As Worksheet 'PP 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")
Set shPP = ThisWorkbook.Sheets("PP")
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:AO1"), 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 = "Acno" Then
If frmForm.cmbSearchColumn.Value = "Year" Or frmForm.cmbSearchColumn.Value = "BFTE" Or frmForm.cmbSearchColumn.Value = "BUnit" Or frmForm.cmbSearchColumn.Value = "BRate" Or _
Trim(frmForm.cmbSearchColumn.Value) = "BAmt" Or Trim(frmForm.cmbSearchColumn.Value) = "FFTE" Or Trim(frmForm.cmbSearchColumn.Value) = "FUnit" Or Trim(frmForm.cmbSearchColumn.Value) = "FRate" Or _
Trim(frmForm.cmbSearchColumn.Value) = "FAmt" Or frmForm.cmbSearchColumn.Value = "AFTE" Or frmForm.cmbSearchColumn.Value = "AUnit" Or frmForm.cmbSearchColumn.Value = "ARate" Or _
frmForm.cmbSearchColumn.Value = "AAmt" Then
shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue
Else
shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & Trim(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
shPP.Cells.Clear
shDatabase.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("SearchDAta").Range("A1")
shDatabase.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("PP").Range("A1")
Application.CutCopyMode = False
iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
frmForm.lstDatabase.ColumnCount = 36
frmForm.lstDatabase.ColumnHeads = True
frmForm.lstDatabase.IntegralHeight = True
frmForm.lstDatabase.MultiSelect = fmMultiSelectSingle
frmForm.lstDatabase.ColumnWidths = "30,60,60,60,60,80,60,60,60,60,60,60,60,60,60,60,60,60,30,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60,60"
frmForm.lstDatabase.Top = 35
If iSearchRow > 1 Then
frmForm.lstDatabase.RowSource = "SearchData!A2:AO" & iSearchRow
MsgBox "Records found."
shDatabase.AutoFilterMode = False
Application.ScreenUpdating = True
shDatabase.ListObjects("Table1").AutoFilter.ShowAllData
End If
Else
MsgBox "No record found."
End If
shDatabase.AutoFilterMode = False
Application.ScreenUpdating = True
End SubYEAR, BFTE, BUNIT, BRATE, BAMT, FFTE, FUNIT, FRATE, FAMT, AFTE, AUNIT, ARATE, AAMT are all numeric columns but in list box appears as string and hence if I key a value with the format then it gives the search result. I want to input value without the format, just pure values.
Thank you in advance
