Search And Filter Cases

[JUES]

Good afternoon friends, I hope you are well.
I am trying to modify the RPAT form (frmrpat) to suit my needs however I cannot resolve the following details:

1. Adapt the following macro from a YouTube tutorial

>>> use code - tags <<<

Private Sub CmdBUSCAR_Click()

Dim UFILA As Integer

If Me.TextBox11 = "" Then
MsgBox "Ingrese el Número de Cédula", vbExclamation
End If
Exit Sub

ROW = Sheets("MATRIZ").Range("B" & Rows.Count).End(xlUp).Row

Me.ListBox1.RowSource = Clear
For Row = 2 To UFILA

If UCase(Range("B" & Row).Value) Like "*" & UCase(Me.TextBox11.Value) & "*" Then

Me.ListBox1.AddItem
Me.ListBox1.List(ListBox1.ListCount - 1, 0) = Cells(Row, 2)
Me.ListBox1.List(ListBox1.ListCount - 1, 19) = Cells(Row, 21)
Me.ListBox1.List(ListBox1.ListCount - 1, 23) = Cells(Row, 25)
Me.ListBox1.List(ListBox1.ListCount - 1, 24) = Cells(Row, 26)
Me.ListBox1.List(ListBox1.ListCount - 1, 45) = Cells(Row, 47)
End If

Next Row

Me.TextBox11.Value = Empty
Me.TextBox11.SetFocus

End Sub

to search for the cases by the ID number and show the results in ListBox1, however it asks me to declare the variables Clear and Row and when doing so it does not carry out any kind of search and in ListBox1 all the cases present in the list continue to appear. Sheet3 (MATRIZ).

2. Depending on the case (AT or EO) that has been selected in Combobox1, ListBox1 should filter and reflect the number of cases associated with the identification number searched in TextBox11 when in Sheet3 (MATRIZ) AU=”” and AU <>””

 

[Marc L]

Hi, some codelines useless or no sense … As you forgot to allocate the variable UFILA so nothing is done !​

(And before the very misplaced Exit Sub !)​

​

An easier way according to VBA help and just using the Macro Recorder :​

allocate the data range to RowSource and filter the range according to the UserForm query.​

​

 

[JUES]

Greetings my friend, I hope you are well, thank you for answering my concern. I don't know how to do what you tell me. I have little knowledge of Vba. If you could help me by making the corrections in the attached file, I would be very grateful.

 

[Marc L]

As it works only with smart ranges so according to your attachment :​

first delete the last row - as empty - of the source data table,​

the LISTAS worksheet from column F is used for the filtered data with the below sample.​

​

To see the headers then you just have to set the ListBox ColumnHeads property to True.​

Set also the ColumnCount property to 5 according to the below sample.​

​

My two cents : TextBox11 should be more user friendly as a ComboBox …​

​

For starters replace all the UserForm module with this Excel basics table filter sample :​

Private Sub CmdBUSCAR_Click()
        ListBox1.RowSource = ""
        Hoja1.[F1].CurrentRegion.Clear
   With Hoja3.ListObjects(1).Range
        If TextBox11 = "" Then .AutoFilter 1 Else .AutoFilter 1, TextBox11
        If ComboBox1.ListIndex < 1 Then .AutoFilter 46 Else .AutoFilter 46, Array("=", "<>")(ComboBox1.ListIndex - 1)
       .Range(Replace("A1:A#,T1:T#,X1:Y#,AT1:AT#", "#", .Rows.Count)).Copy Hoja1.[F1]
   End With
        ListBox1.RowSource = Hoja1.[F1].CurrentRegion.Offset(1).Address(, , , 1)
End Sub

Private Sub ListBox1_Click()
    TextBox11 = ListBox1
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.RowSource = Hoja1.[B2].CurrentRegion.Address(, , , 1)
    Hoja3.ListObjects(1).AutoFilter.ShowAllData
    CmdBUSCAR_Click
End Sub

Private Sub UserForm_Terminate()
    Hoja1.[F1].CurrentRegion.Clear
    Hoja3.ListObjects(1).AutoFilter.ShowAllData
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​