Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

exclude zero for data and precede it on userform when search

0

Hello

I no  know  if  what  I  search for  it  could  be  possible .

I want  when select sheet  from combobox1 , then  will just show data  don't  contain zero  for  lastrow  and  ignores  the  whole data  contains zero  and  what  precede it .

and  when I  search in textbox1 within  listbox  shouldn't  return the  whole  data  as  in  sheet , just  depends on populated data in listbox  as  the  original  code does it .

sorry  about  poor info, but  I  hope  the  two  pictures are  in PURCHASE sheet  as  in  example help  you  to  understand  what  I look for 

I  hope  anybody  could  help despite  of it's  not  to  easy

thanks 

Answer
Discuss

Answers

0
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.

Discuss

Discussion

Sorry John !
this  is  not  what  I  want.
If you don't want to display rows with 0.00 in column I (9)
not  this  only , also  any  row  contains zero  should  also ignore the  all of data are  before  row  contains  zero.
just concentrate two pictures you will see just show  data after row contains zero.
Kalil (rep: 36) May 23, '23 at 9:14 am
Okay. Please see revision to Answer and second file.
John_Ru (rep: 6172) May 23, '23 at 10:00 am
awesome  !
thanks   very  much  for  your  time  & help .
Kalil (rep: 36) May 23, '23 at 12:25 pm
Glad that worked for you. Thanks for selecting my Answer, Kalil. 
John_Ru (rep: 6172) May 23, '23 at 1:18 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login