How to code numeric value search into the list box?

lbhandary

New Member
Joined
Mar 17, 2015
Messages
10
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 :)

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 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
 
@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.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,223,970
Messages
6,175,710
Members
452,667
Latest member
vanessavalentino83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top