Search function not finding rows containing the matching value

[Excelhelp_0912]

hi everyone,

I am currently building a userform to save data to a database in the same file. The userform saves the data and also displays it on the userform in a listbox called lstDatabase. One of the features is that you can select one of the rows and edit it. However, you also need to be able to search per category/header and after clicking the search button it should display all the rows with an exact match of that value. I have built a live updating small listbox below the search bar so that when you type and the right value pops up it is copied to the search bar if you double click it. So far so good. However, if I click the search button it only sometimes works with the first column called "Dossier" and does not work at all with the other columns. The uploaded image shows what happens after is search a value of the column "Dossier" and even though there's clearly a row containg that value, it says "no Record found". Anyone have a solution?

this is the code for the userform with some subs deleted because otherwise it is too big

Option Explicit
Public EnableEvents As Boolean
Dim criterion


Private Sub cmbSearchColumn_Change()
    Dim c As Integer
    Dim colheaders
    colheaders = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y")
    
    For c = 1 To 25
        If ThisWorkbook.Sheets("Database").Cells(1, c).Value = Me.cmbSearchColumn.Value Then
        criterion = colheaders(c - 1)
        End If
        Next
    
    
    If Me.EnableEvents = False Then Exit Sub
    
    If Me.cmbSearchColumn.Value = "All" Then
        
        Call Reset
    Else
        
        Me.txtSearch.Value = ""
        Me.txtSearch.Enabled = True
        Me.cmdSearch.Enabled = True
        
    End If
    
    Me.ListBox1.Clear
    Me.txtSearch.Value = ""
    Me.txtSearch.SetFocus

End Sub

Private Sub cmdSearch_Click()

    If Me.txtSearch.Value = "" Then
        
        MsgBox "Please enter the search value.", vbOKOnly + vbInformation, "Search"
        Exit Sub
    
    End If
    
    Call SearchData
    

End Sub

And here is the relevant Module1 vba code:

Function Selected_List() As Long

    Dim i As Long
    Selected_List = 0
    
    For i = 0 To UserForm1.lstDatabase.ListCount - 1
    
        If UserForm1.lstDatabase.Selected(i) = True Then
        
            Selected_List = i + 1
            Exit For
        End If
        
    Next i


End Function


Sub Add_SearchColumn()
    
    UserForm1.EnableEvents = False
    
    With UserForm1.cmbSearchColumn
        
        .Clear
        
        .AddItem "All"
        
        .AddItem "Dossier"
        .AddItem "Crediteur"
        .AddItem "Kosten plaats"
        .AddItem "Omschrijving"
        .AddItem "Eenheid"
        .AddItem "Geleverd gewicht in aantal tonnen"
        .AddItem "Inkoopprijs per ton"
        .AddItem "Inkoopwaarde"
        .AddItem "Debiteur"
        .AddItem "Klant ordernummer"
        .AddItem "Verkoopordernummer"
        .AddItem "Factuur"
        .AddItem "Verkoopprijs per ton"
        .AddItem "Theoretisch geleverd gewicht"
        .AddItem "Kosten Inkoopboeking Unit4"
        .AddItem "Gevraagde levertijd inkoop"
        .AddItem "Werkelijke levertijd inkoop"
        .AddItem "Verkoopboeking Unit4"
        .AddItem "Factuurnummer Unit4"
        .AddItem "Gevraagde levertijd verkoop"
        .AddItem "Werkelijke levertijd verkoop"


        .Value = "All"
        
    End With
    
    UserForm1.EnableEvents = True
    
    UserForm1.txtSearch.Value = ""
    UserForm1.txtSearch.Enabled = False
    UserForm1.cmdSearch.Enabled = False
    
End Sub


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 SearchData 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 = UserForm1.cmbSearchColumn.Value
    
    sValue = UserForm1.txtSearch.Value
    
    iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:Y1"), 0)
    
    'Remove filter from Database worksheet
    
    If shDatabase.FilterMode = True Then
        
        shDatabase.AutoFilterMode = False
    
    End If
    
    'Apply filter on Database worksheet
    
    If UserForm1.cmbSearchColumn.Value = xlFilterValues Then
        
        shDatabase.Range("A1:Y" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue, Operator:=xlFilterValues
        
    Else
        
        shDatabase.Range("A1:Y" & 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
        
        UserForm1.lstDatabase.ColumnCount = 25
        
    
        If iSearchRow > 1 Then
            
            UserForm1.lstDatabase.RowSource = "SearchData!A2:Y" & iSearchRow
            
            MsgBox "Records found."
            
        End If
        
    Else
    
        MsgBox "No record found."
    
    End If
    
    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True
    

End Sub

 

[GlennUK]

What debugging did you attempt with your code? For example, did you put a breakpoint in before the "No records found" message to examine the state of the variables etc?