Auto filter a table based on the value of a cell

[R3Z]

Hi all I'm using this code that I saw in another thread:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("C2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("D2")) Is Nothing Then
    Range(Range("B5"), Range("D5").End(xlDown)).AutoFilter Field:=1, Criteria1:=Range("B2").Value & "*"
    Range(Range("B5"), Range("D5").End(xlDown)).AutoFilter Field:=2, Criteria1:=Range("C2").Value & "*"
    Range(Range("B5"), Range("D5").End(xlDown)).AutoFilter Field:=3, Criteria1:=Range("D2").Value & "*"
End If
End Sub

This is when I try to add more rows to the table when I try to search the in B2 I don't get the results for the new data.
I don't know if this helps but the cells that are giving me results show up this error: "this number in this cell is formatted as text or preceded by an apostrophe" the new rows don't show up this error and I can't get the results when I search them on B2 (for example).
Please Help me I not experienced person in Excel but I really need a solution.
Thank You.

 

[R3Z]

OK try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tblData As ListObject
Dim colFltr As Long

If Not Application.Intersect(Target, Range("B2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("C2")) Is Nothing Or _
   Not Application.Intersect(Target, Range("D2")) Is Nothing Then
  
    Application.EnableEvents = False
   
    Set tblData = Me.ListObjects("DataTable")
   
    With tblData.AutoFilter
        If .FilterMode Then .ShowAllData
    End With
   
    With tblData
        colFltr = Target.Column - tblData.Range.Cells(1).Column + 1
        .Range.AutoFilter Field:=colFltr, Criteria1:=Target.Value & "*"
    End With
   
    Application.EnableEvents = True
End If

End Sub

Thanks, I copied and pasted the code, this worked I just need to put the "single quote" before the new numbers on B column in order to the new rows show in the search. Don't know why the older data don't the the " ' " and the new one needs it.
But anyway if you know what I can do in order to do this without using the "single quote" I'll appreciate your time to help me.
You really helped me solve this one. Thank you very muck.

 

[Alex Blakenburg]

At the moment you are using a wildcard search (adding "*") this is incompatible with searching for a numeric value.

Do you need the wild card part of the search ?
Do all columns have both numbers and text that you need to search for ?
Is column B all numeric ?

 

[R3Z]

At the moment you are using a wildcard search (adding "*") this is incompatible with searching for a numeric value.

Do you need the wild card part of the search ?
Do all columns have both numbers and text that you need to search for ?
Is column B all numeric ?

I need to search columns B,C and D.
Column B it's all numeric, C and D are text.

At the moment you are using a wildcard search (adding "*") this is incompatible with searching for a numeric value.

Do you need the wild card part of the search ?
Do all columns have both numbers and text that you need to search for ?
Is column B all numeric ?

Hi, is there a way to do the code like as I type in the cells the results starts showing up?

 

[Alex Blakenburg]

In the cell possibly in the filter results would be much harder. Either way you would be better off starting a new thread. I don't know much about that sort of thing.