Selected Answer
Hi Kalil
If you don't want to display rows with 0.00 in column I (9), you can add an extra test (when TextBox1 is changed) to add items only when va(i,9) isn't $0.00- see changes in bold below (commenting out your original test againt tx)
Private Sub TextBox1_Change()
Dim i As Long, j As Long, k As Long
Dim tx As String
Dim vb
tx = Trim(UCase(TextBox1.Text))
If oldValue = tx Then Exit Sub
If ComboBox1.Value = "" Or IsEmpty(va) Then ListBox1.Clear: Exit Sub
If tx = "" Then ListBox1.List = vc: Exit Sub
tx = "*" & Replace((tx), " ", "*") & "*"
ReDim vb(1 To UBound(va, 2), 1 To UBound(va, 1))
For i = 1 To UBound(va, 1)
'If UCase(va(i, 4)) Like tx then
' add a test for value column
If UCase(va(i, 4)) Like tx And va(i, 9) <> "$0.00" Then
k = k + 1
For j = 1 To UBound(va, 2)
vb(j, k) = va(i, j)
Next
If k = 100 Then Exit For
End If
Next
Select Case k
Case 0
ListBox1.Clear
Case 1
ReDim Preserve vb(1 To UBound(va, 2), 1 To 2)
ListBox1.List = Application.Transpose(vb)
Case Is > 1
ReDim Preserve vb(1 To UBound(va, 2), 1 To k)
ListBox1.List = Application.Transpose(vb)
End Select
oldValue = tx
End Sub
That's done in the FIRST attached file- pick PURCHASE then type INV-1004 and you'll see that row 9 data is not displayed (since cell I9=0).
Revision 23 May 2023
If however you want to display only entries after column I last went to 0 (presumably when the account balance was settled), the SECOND file below has the revised code below. It first runs backwards through array va until it finds a zero entry. It then populates the list from the entry after that (see changes in bold):
Private Sub TextBox1_Change()
Dim i As Long, j As Long, k As Long
Dim tx As String
Dim vb
tx = Trim(UCase(TextBox1.Text))
If oldValue = tx Then Exit Sub
If ComboBox1.Value = "" Or IsEmpty(va) Then ListBox1.Clear: Exit Sub
If tx = "" Then ListBox1.List = vc: Exit Sub
tx = "*" & Replace((tx), " ", "*") & "*"
ReDim vb(1 To UBound(va, 2), 1 To UBound(va, 1))
' find last row with 0.00 in column i
i = UBound(va, 1) + 1
Do
i = i - 1
Loop Until va(i, 9) = "$0.00" Or i = 1
' populate list from row after last zero (if any)
For i = i + 1 To UBound(va, 1)
If UCase(va(i, 4)) Like tx Then
k = k + 1
For j = 1 To UBound(va, 2)
vb(j, k) = va(i, j)
Next
If k = 100 Then Exit For
End If
Next
Select Case k
Case 0
ListBox1.Clear
Case 1
ReDim Preserve vb(1 To UBound(va, 2), 1 To 2)
ListBox1.List = Application.Transpose(vb)
Case Is > 1
ReDim Preserve vb(1 To UBound(va, 2), 1 To k)
ListBox1.List = Application.Transpose(vb)
End Select
oldValue = tx
End Sub
Hope this is what you meant.
