populate data in listbox based on multiple textboxes instead one textbox

[abdo meghari]

hi

I need modified this code to become searching based on multiple textboxes (textbox1,2,3)= COLS( D,E,F) . currently the code works based on COL D with textbox1

Private Sub TextBox1_Change()
    Dim myArray, lr, x, i
    Dim DATA As Worksheet
    Set DATA = Worksheets("purchase")
    lr = DATA.Cells(Rows.Count, 4).End(xlUp).Row
    ListBox1.Clear
    If TextBox1.Text = "" Then Exit Sub
    myArray = DATA.Range("A2:G" & lr + 1)
    ReDim y(1 To UBound(myArray) * 7, 1 To 7)
    For i = LBound(myArray) To UBound(myArray)
     a = Len(Me.TextBox1.Text)
     For x = 1 To 7
     If Left(myArray(i, x), a) = Left(TextBox1.Text, a) Then
            rw = rw + 1
            For yy = 1 To 7
                y(rw, yy) = myArray(i, yy)
            Next yy
        End If
        Next
    Next i
    If rw > 0 Then
        ListBox1.List = y()
    End If
End Sub

any help would be appreciate

 

[DanteAmor]

Try this:

Sub FilterData()
  Dim txt1 As String, txt2 As String, txt3 As String
  Dim i As Long, j As Long, k As Long
  
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    If TextBox1 = "" Then txt1 = a(i, 4) Else txt1 = TextBox1
    If TextBox2 = "" Then txt2 = a(i, 5) Else txt2 = TextBox2
    If TextBox3 = "" Then txt3 = a(i, 6) Else txt3 = TextBox3
    If LCase(a(i, 4)) Like LCase(txt1) & "*" And _
       LCase(a(i, 5)) Like LCase(txt2) & "*" And _
       LCase(a(i, 6)) Like LCase(txt3) & "*" Then
      k = k + 1
      For j = 1 To 7
        If j = 1 Then 'Change the 1 in this line for the column number where you have the date
          b(k, j) = Format(a(i, j), "dd/mm/yyyy")
        Else
          b(k, j) = a(i, j)
        End If
      Next
    End If
  Next
  If k > 0 Then ListBox1.List = b
End Sub

 

[Tom.Jones]

@DanteAmor,

Thank you so much.