Need HELP with listbox search

[johnsonk]

Hi All, I'm very new to VBA I have created a simple UserForm with just TextBox1, ListBox1 and CommandButton1. I have a sheet with data called IngData which has 10 columns and rows will be to how ever fare they will go I have some VBA coding already but my problem is my search results is only showing me one result and not all that have the same PLU number which is column in A could anyone advise me how to make this happen please. See below the coding I have at the minute.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub commandbutton1_click()
Dim i As Long
Me.ListBox1.Clear
Me.ListBox1.AddItem
For A = 1 To 10
Me.ListBox1.List(0, A - 1) = Sheet8.Cells(1, A)
Next A
Me.ListBox1.Selected(0) = True
For i = 2 To Sheet8.Range("A1000000").End(xlUp).Offset(1, 0).Row
For j = 1 To 10
h = Application.WorksheetFunction.CountIf(Sheet8.Range("A" & 2, "J" & i), _
Sheet8.Cells(i, j))
If h = 1 And LCase(Sheet8.Cells(i, j)) = LCase(Me.TextBox1) Or h = 1 And _
Sheet8.Cells(i, j) = Val(Me.TextBox1) Then
Me.ListBox1.AddItem
For x = 1 To 10
Me.ListBox1.List(ListBox1.ListCount - 1, x - 1) = Sheet8.Cells(i, x)
Next x
End If
Next j
Next i[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]End Sub

Regards
Kenny
[/FONT]

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()
    Dim i As Long, j As Long
    
    Me.ListBox1.Clear
    Me.ListBox1.AddItem
    For A = 1 To 10
        Me.ListBox1.List(0, A - 1) = Sheet8.Cells(1, A)
    Next A
    For i = 2 To Sheet8.Range("A" & Rows.Count).End(xlUp).Row
        If LCase(Sheet8.Cells(i, "A").Value) = LCase(Me.TextBox1) Then
            Me.ListBox1.AddItem
            For j = 1 To 10
                Me.ListBox1.List(ListBox1.ListCount - 1, j - 1) = Sheet8.Cells(i, j).Value
            Next j
        End If
    Next i
End Sub

 

[johnsonk]

Hi,

I have tried changing this code so it shows 11 columns insted of 10 but i keet getting an error can anyone help.

Private Sub CommandButton1_Click()
Dim i As Long, j As Long

Me.ListBox1.Clear
Me.ListBox1.AddItem
For A = 1 To 11
Me.ListBox1.List(0, A - 1) = Sheet8.Cells(1, A)
Next A
For i = 2 To Sheet8.Range("A" & Rows.Count).End(xlUp).Row
If LCase(Sheet8.Cells(i, "A").Value) = LCase(Me.TextBox1) Then
Me.ListBox1.AddItem
For j = 1 To 11
Me.ListBox1.List(ListBox1.ListCount - 1, j - 1) = Sheet8.Cells(i, j).Value
Next j
End If
Next i
End Sub

 

[DanteAmor]

With the .Additem method you can only add 10.
You have to use the .Rowsource or .List property to add more columns.