Search code examples
excelvbasearchlistboxuserform

VBA - Search not showing multiple records in Listbox

 Private Sub CommandButton10_Click()

'listbox column headers
 Me.ListBox1.AddItem
 For A = 1 To 7
 Me.ListBox1.List(0, A - 1) = Sheet2.Cells(1, A)
 Next A
 Me.ListBox1.Selected(0) = True

'Populating listbox from search
 Dim i As Long
 For i = 2 To Sheet2.Range("A100000").End(xlUp).Offset(1, 0).Row
 For j = 1 To 7
 H = Application.WorksheetFunction.CountIf(Sheet2.Range("A" & 2, "G" & i), _
 Sheet2.Cells(i, j))
 If H = 1 And LCase(Sheet2.Cells(i, j)) = LCase(Me.TextBox2) Or H = 1 And _
 Sheet2.Cells(i, j) = Val(Me.TextBox2) Then
 Me.ListBox1.AddItem
 For x = 1 To 7
 Me.ListBox1.List(ListBox1.ListCount - 1, x - 1) = Sheet2.Cells(i, x)
 Next x
 End If
 Next j
 Next i

'Count the listbox rows when populated
 With Me.ListBox1
 For x = 0 To .ListCount - 1
    TextBox3 = x
 Next x
 End With

End Sub

Hello all, I am in need of help please, Can anyone tell me why my code wont search for everything in the worksheet relating to a text e.g. there are 2 same items in the worksheet with different serial numbers but when i search for the item, only 1 record shows. Thanks.

Solution

  • I think you should change:

     H = Application.WorksheetFunction.CountIf(Sheet2.Range("A" & 2, "G" & i), Sheet2.Cells(i, j))

    to:

     H = Application.WorksheetFunction.CountIf(Sheet2.Range("A" & i, "G" & i), Sheet2.Cells(i, j))

    so that:

    • the match is being searched row by row

    • following check If H = 1 And LCase(Sheet2.Cells(i, j)) ... isn't excluding any possible multiple occurrences of the same item (which would return H>1 if counted from row 2 downwards)

    note: this way you'll miss any possible match for item having multiple occurrences in the same row