How to code numeric value search into the list box?

[lbhandary]

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

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 Sub

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

 

[smozgur]

@lbhandary - Please mark the post that answered your solution in your future posts instead of your feedback post. It will help future readers.
I marked @DanteAmor's answer as the solution for this question.

 

[lbhandary]

@lbhandary - Please mark the post that answered your solution in your future posts instead of your feedback post. It will help future readers.
I marked @DanteAmor's answer as the solution for this question.

Thanks for checking, I thought I had marked as solution. I did now.

 

[smozgur]

Thanks for checking, I thought I had marked as solution. I did now.

You did the same thing, and marked your own post, not the answer.
I marked the right solution, once again. So, please do not change it.

Read Mark as Solution for more information.