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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,567
Office Version
  1. 2007
Platform
  1. Windows
I made some adjustments to your code:

VBA Code:
Sub SearchData()
  Dim shDatabase As Worksheet   ' Database sheet
  Dim shSearchData As Worksheet 'SearchData sheet
  Dim shPP As Worksheet         'PP sheet
  Dim iColumn As Long           '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")
  
  Application.ScreenUpdating = False
  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
  'Apply filter on Database worksheet
  Select Case UCase(sColumn)
    Case "YEAR", "BFTE", "BUNIT", "BRATE", "BAMT", "FFTE", "FUNIT", "FRATE", _
         "FAMT", "AFTE", "AUNIT", "ARATE", "AAMT"
      shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, CDbl(sValue)
    Case Else
      shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, "*" & Trim(sValue) & "*"
  End Select
    
  'Code to remove the previous data from SearchData worksheet
  shSearchData.Cells.Clear
  shPP.Cells.Clear
    
  If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then
    With shDatabase.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible)
      .Copy shSearchData.Range("A1")
      .Copy shPP.Range("A1")
    End With
    iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
    With frmForm.lstDatabase
      .ColumnCount = 36
      .ColumnHeads = True
      .IntegralHeight = True
      .MultiSelect = fmMultiSelectSingle
      .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"
      .Top = 35
      If iSearchRow > 1 Then .RowSource = "'" & shSearchData.Name & "'!A2:AO" & iSearchRow
    End With
  Else
    MsgBox "No record found."
  End If
  shDatabase.ListObjects("Table1").AutoFilter.ShowAllData
  shDatabase.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub
 

lbhandary

New Member
Joined
Mar 17, 2015
Messages
10
Sub SearchData() Dim shDatabase As Worksheet ' Database sheet Dim shSearchData As Worksheet 'SearchData sheet Dim shPP As Worksheet 'PP sheet Dim iColumn As Long '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") Application.ScreenUpdating = False 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 'Apply filter on Database worksheet Select Case UCase(sColumn) Case "YEAR", "BFTE", "BUNIT", "BRATE", "BAMT", "FFTE", "FUNIT", "FRATE", _ "FAMT", "AFTE", "AUNIT", "ARATE", "AAMT" shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, CDbl(sValue) Case Else shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, "*" & Trim(sValue) & "*" End Select 'Code to remove the previous data from SearchData worksheet shSearchData.Cells.Clear shPP.Cells.Clear If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then With shDatabase.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible) .Copy shSearchData.Range("A1") .Copy shPP.Range("A1") End With iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row With frmForm.lstDatabase .ColumnCount = 36 .ColumnHeads = True .IntegralHeight = True .MultiSelect = fmMultiSelectSingle .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" .Top = 35 If iSearchRow > 1 Then .RowSource = "'" & shSearchData.Name & "'!A2:AO" & iSearchRow End With Else MsgBox "No record found." End If shDatabase.ListObjects("Table1").AutoFilter.ShowAllData shDatabase.AutoFilterMode = False Application.ScreenUpdating = True End Sub
Thanks for responding and cleaning up my shabby work. Unfortunately now it does not pick up the amount nor in the formatted value input which happened earlier. I took care that header BAMT etc matched my datasheet. It still finds the text.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,567
Office Version
  1. 2007
Platform
  1. Windows
How does the amount look in the textbox?
How does the amount look on the sheet?


I made a little change, try again:
VBA Code:
Sub SearchData()
  Dim shDatabase As Worksheet   ' Database sheet
  Dim shSearchData As Worksheet 'SearchData sheet
  Dim shPP As Worksheet         'PP sheet
  Dim iColumn As Long           '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
  Dim dValue As Double
  
  Set shDatabase = ThisWorkbook.Sheets("Database")
  Set shSearchData = ThisWorkbook.Sheets("SearchData")
  Set shPP = ThisWorkbook.Sheets("PP")
  
  Application.ScreenUpdating = False
  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
  'Apply filter on Database worksheet
  Select Case UCase(sColumn)
    Case "YEAR", "BFTE", "BUNIT", "BRATE", "BAMT", "FFTE", "FUNIT", "FRATE", _
         "FAMT", "AFTE", "AUNIT", "ARATE", "AAMT"
      dValue = CDbl(sValue)
      sValue = Format(dValue, shDatabase.Cells(2, iColumn).NumberFormat)
      shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, sValue
    Case Else
      shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, "*" & Trim(sValue) & "*"
  End Select
    
  'Code to remove the previous data from SearchData worksheet
  shSearchData.Cells.Clear
  shPP.Cells.Clear
    
  If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then
    With shDatabase.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible)
      .Copy shSearchData.Range("A1")
      .Copy shPP.Range("A1")
    End With
    iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
    With frmForm.lstDatabase
      .ColumnCount = 36
      .ColumnHeads = True
      .IntegralHeight = True
      .MultiSelect = fmMultiSelectSingle
      .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"
      .Top = 35
      If iSearchRow > 1 Then .RowSource = "'" & shSearchData.Name & "'!A2:AO" & iSearchRow
    End With
  Else
    MsgBox "No record found."
  End If
  shDatabase.ListObjects("Table1").AutoFilter.ShowAllData
  shDatabase.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub
 

lbhandary

New Member
Joined
Mar 17, 2015
Messages
10

ADVERTISEMENT

How does the amount look in the textbox?
How does the amount look on the sheet?


I made a little change, try again:
VBA Code:
Sub SearchData()
  Dim shDatabase As Worksheet   ' Database sheet
  Dim shSearchData As Worksheet 'SearchData sheet
  Dim shPP As Worksheet         'PP sheet
  Dim iColumn As Long           '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
  Dim dValue As Double
 
  Set shDatabase = ThisWorkbook.Sheets("Database")
  Set shSearchData = ThisWorkbook.Sheets("SearchData")
  Set shPP = ThisWorkbook.Sheets("PP")
 
  Application.ScreenUpdating = False
  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
  'Apply filter on Database worksheet
  Select Case UCase(sColumn)
    Case "YEAR", "BFTE", "BUNIT", "BRATE", "BAMT", "FFTE", "FUNIT", "FRATE", _
         "FAMT", "AFTE", "AUNIT", "ARATE", "AAMT"
      dValue = CDbl(sValue)
      sValue = Format(dValue, shDatabase.Cells(2, iColumn).NumberFormat)
      shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, sValue
    Case Else
      shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, "*" & Trim(sValue) & "*"
  End Select
  
  'Code to remove the previous data from SearchData worksheet
  shSearchData.Cells.Clear
  shPP.Cells.Clear
  
  If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then
    With shDatabase.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible)
      .Copy shSearchData.Range("A1")
      .Copy shPP.Range("A1")
    End With
    iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
    With frmForm.lstDatabase
      .ColumnCount = 36
      .ColumnHeads = True
      .IntegralHeight = True
      .MultiSelect = fmMultiSelectSingle
      .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"
      .Top = 35
      If iSearchRow > 1 Then .RowSource = "'" & shSearchData.Name & "'!A2:AO" & iSearchRow
    End With
  Else
    MsgBox "No record found."
  End If
  shDatabase.ListObjects("Table1").AutoFilter.ShowAllData
  shDatabase.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub
Thanks, does not work. Can I just share my file?.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,567
Office Version
  1. 2007
Platform
  1. Windows
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

And you explain to me how I should execute the code or the userform.
 

lbhandary

New Member
Joined
Mar 17, 2015
Messages
10

ADVERTISEMENT

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

And you explain to me how I should execute the code or the userform.
Super. Here's the link.

On launching the form, I want to search row based on amount. I am failing to search with columns having numbers. Text search is a success. Rest I am sure you will pick up once you see the module.
Hence seeking correction in my codes so that search can be made by inputting values and not values in the formatted form.

In the same breath how can i fix the form size on initialization. Restore and full screen works fine but every time i modify the codes, the form goes on shrinking. (well this change is not my focus).

Thanks in advance.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,567
Office Version
  1. 2007
Platform
  1. Windows
Hence seeking correction in my codes so that search can be made by inputting values and not values in the formatted form.

I tested and it works with both plain numbers and formatted numbers.

I changed the property to false so that the size of the listbox is not changed.
.IntegralHeight = False

Try this:

VBA Code:
Sub SearchData()
  Dim shDatabase As Worksheet   ' Database sheet
  Dim shSearchData As Worksheet 'SearchData sheet
  Dim shPP As Worksheet         'PP sheet
  Dim iColumn As Long           '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
  Dim dValue As Double
  
  Set shDatabase = ThisWorkbook.Sheets("Database")
  Set shSearchData = ThisWorkbook.Sheets("SearchData")
  Set shPP = ThisWorkbook.Sheets("PP")
  
  Application.ScreenUpdating = False
  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
  'Apply filter on Database worksheet
  Select Case UCase(sColumn)
    Case "YEAR", "BFTE", "BUNIT", "BRATE", "BAMT", "FFTE", "FUNIT", "FRATE", _
         "FAMT", "AFTE", "AUNIT", "ARATE", "AAMT"
         
      dValue = Val(sValue)
      If shDatabase.Cells(2, iColumn).NumberFormat = "General" Then
        shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, dValue
      Else
        'Format(1, "#,##0.00")
        sValue = Format(dValue, "#,##0.00")
        shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, sValue
      End If
    Case Else
      shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, "*" & Trim(sValue) & "*"
  End Select
    
  'Code to remove the previous data from SearchData worksheet
  shSearchData.Cells.Clear
  shPP.Cells.Clear
  frmForm.lstDatabase.RowSource = ""
  If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then
    With shDatabase.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible)
      .Copy shSearchData.Range("A1")
      .Copy shPP.Range("A1")
    End With
    iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
    With frmForm.lstDatabase
      .ColumnCount = 36
      .ColumnHeads = True
      .IntegralHeight = False
      .MultiSelect = fmMultiSelectSingle
      .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"
      .Top = 35
      If iSearchRow > 1 Then .RowSource = "'" & shSearchData.Name & "'!A2:AO" & iSearchRow
    End With
  Else
    MsgBox "No record found."
  End If
  shDatabase.ListObjects("Table1").AutoFilter.ShowAllData
  shDatabase.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub
 
Solution

lbhandary

New Member
Joined
Mar 17, 2015
Messages
10
I tested and it works with both plain numbers and formatted numbers.

I changed the property to false so that the size of the listbox is not changed.
.IntegralHeight = False

Try this:

VBA Code:
Sub SearchData()
  Dim shDatabase As Worksheet   ' Database sheet
  Dim shSearchData As Worksheet 'SearchData sheet
  Dim shPP As Worksheet         'PP sheet
  Dim iColumn As Long           '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
  Dim dValue As Double
 
  Set shDatabase = ThisWorkbook.Sheets("Database")
  Set shSearchData = ThisWorkbook.Sheets("SearchData")
  Set shPP = ThisWorkbook.Sheets("PP")
 
  Application.ScreenUpdating = False
  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
  'Apply filter on Database worksheet
  Select Case UCase(sColumn)
    Case "YEAR", "BFTE", "BUNIT", "BRATE", "BAMT", "FFTE", "FUNIT", "FRATE", _
         "FAMT", "AFTE", "AUNIT", "ARATE", "AAMT"
        
      dValue = Val(sValue)
      If shDatabase.Cells(2, iColumn).NumberFormat = "General" Then
        shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, dValue
      Else
        'Format(1, "#,##0.00")
        sValue = Format(dValue, "#,##0.00")
        shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, sValue
      End If
    Case Else
      shDatabase.Range("A1:AO" & iDatabaseRow).AutoFilter iColumn, "*" & Trim(sValue) & "*"
  End Select
   
  'Code to remove the previous data from SearchData worksheet
  shSearchData.Cells.Clear
  shPP.Cells.Clear
  frmForm.lstDatabase.RowSource = ""
  If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then
    With shDatabase.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible)
      .Copy shSearchData.Range("A1")
      .Copy shPP.Range("A1")
    End With
    iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
    With frmForm.lstDatabase
      .ColumnCount = 36
      .ColumnHeads = True
      .IntegralHeight = False
      .MultiSelect = fmMultiSelectSingle
      .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"
      .Top = 35
      If iSearchRow > 1 Then .RowSource = "'" & shSearchData.Name & "'!A2:AO" & iSearchRow
    End With
  Else
    MsgBox "No record found."
  End If
  shDatabase.ListObjects("Table1").AutoFilter.ShowAllData
  shDatabase.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub
Dear DanteAmor, Thanks a lot. I did not try yet but I believe you:). Actually I am too too too amateur, I took some basic coding from internet and made it to this level tweeting here and there. Excel is just my hobby. I appreciate your quick reaction. Thanks again. Cheers!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,567
Office Version
  1. 2007
Platform
  1. Windows
Actually I am too too too amateur, I took some basic coding from internet and made it to this level tweeting here and there.
I think it's the way we all learn, and in fact, we continue to learn.
 

Forum statistics

Threads
1,141,000
Messages
5,703,652
Members
421,308
Latest member
NewBlood

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
Top