VBA Form Search Box

[leonardtan]

Dear VBA Experts,

I have created the form to search database but encountered this issue. The search cannot list full details but only 1 search is found where it appears to be 2 on the database.

Could you advise where is wrong with my code

Please. Thanks

Private Sub CommandButton1_Click()
Dim i As Long
On Error Resume Next
Me.TextBox1.Text = StrConv(Me.TextBox1.Text, vbProperCase)
Me.ListBox1.Clear
'For Column Header
Me.ListBox1.AddItem
Me.ListBox1.ColumnWidths = "2cm;4cm;2cm;3cm;4cm;4cm;4cm;10cm;4cm;4cm"
For a = 1 To 9
Me.ListBox1.List(0, a - 1) = Sheet1.Cells(1, a)
Next a
Me.ListBox1.Selected(0) = True

'for listbox fill
For i = 2 To Sheet1.Range("A1000000").End(xlUp).Offset(1, 0).Row
For J = 1 To 9
H = Application.WorksheetFunction.CountIf(Sheet1.Range("A" & 2, "I" & i), _
Sheet1.Cells(i, J))
If H = 1 And LCase(Sheet1.Cells(i, J)) = LCase(Me.TextBox1) Or H = 1 And _
Sheet1.Cells(i, J) = Val(Me.TextBox1) Then
Me.ListBox1.AddItem
For X = 1 To 9
Me.ListBox1.List(ListBox1.ListCount - 1, X - 1) = Sheet1.Cells(i, X)
Next X
End If
Next J
Next i

End Sub

 

[Marc L]

Hi !

Bad logic : why using H variable in your test ? Just try without …
​

 

[Belleke]

I don't like addItem to fill a listbox, I think RemoveItem is a better way to go.
Maybe this helps.
See attached
Type superw or superm in the search box and see what happens.

 

[leonardtan]

Hi !

Bad logic : why using H variable in your test ? Just try without …
​

Ok will try. Thanks for the help

 

[leonardtan]

I don't like addItem to fill a listbox, I think RemoveItem is a better way to go.
Maybe this helps.
See attached
Type superw or superm in the search box and see what happens.

Yes you have improved the script and it works out better thanks