Contains Filter VBA

[pure vito]

Hi All,

I have this code that allows me to search trough a list of items and it works great however some of those items contain two words and the filter doesn't
search for that is someone able to amend this code to use a contains filter please, Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
  Application.ScreenUpdating = False
  Range("B10").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Range("B4").Select
   If Target.Address = Range("B4").Address Then
       Range("B11:i30000").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B3:B4")
       Application.ScreenUpdating = True
   End If
End Sub

 

[Alex Blakenburg]

Unless I am misunderstanding this, the filter is using what is in B4 to filter the data.
If you want it to filter based on contains, simply surround you word with "*" in B4.
eg if you are trying to find "test"and it could be in a sentence then put *test* in B4

 

[pure vito]

That is correct and great advice thank you, however i am sharing this sheet with others that will have the same problem is it possible to have the code do this without adding * thanks for your time

 

[Alex Blakenburg]

If B3 is always going to have the same Column name in it ie you are only ever going to filter on the same column, then switch from AdvancedFilter to AutoFilter.
If the heading changes then the code would need to add the "*" to the value entered in B4.

 

[pure vito]

Thanks again Alex admittedly I am a novice, I have tried replacing AdvancedFilter with AutoFilter with no luck, my intension is to keep the same Column name,
I'm just having a tough time replacing anything in the current code to make this work without having to type in "*" for every search

 

[Peter_SSs]

What about you show us some small (say B3:I20) dummy, but representative, sample data, with XL2BB and explain again in relation to that sample data?

 

[pure vito]

Hi Peter, Thanks for taking the time, I'm not fully understanding you meaning apologies, but I have added these pictures the "name" box is B3 and search bar is B4 when I type
in a name it automatically searches, the first image is blank without a search the second image is using the code I have posted above but as you will see in the third image it doesn't
give me all the information I need ie if I search Charizard and not type in *Charizard then Blains Charizard doesn't show up, this is why I need the contains search, I hope this makes sense

 

[Peter_SSs]

Unfortunately we cannot copy from an image to test - hence my suggestion for XL2BB.

In any case I would suggest a change to AutoFilter as Alex suggested above. Perhaps something like this

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$4" Then
    If ActiveSheet.FilterMode Then ShowAllData
    If Len(Target.Value) > 0 Then Range("B10").CurrentRegion.AutoFilter Field:=1, Criteria1:="*" & Target.Value & "*"
  End If
End Sub

 

[pure vito]

That works perfectly thank you Peter/Alex, I just wasn't able to apply the solution on my own , but I did look at the guide to install XL2BB, but I had difficulty with that to
I'm currently working on my work computer and I hit a few administration walls trying to install it apologies for that it would be handy for all to have that option to be able
to easily share, Thanks again i can move forward onto the next issue now

 

[Alex Blakenburg]

@Peter_SSs but but but .... I was busy writing all the reasons why we needed the XL2BB . Mine looks just like your although I didn't think to test for no data and I did add screenupdating and enableevent.

I'm not fully understanding you meaning apologies

Peter provided a link the XL2BB web page both under the word and in his sign off.
Images mean we have to manually create the data. It also often also does not provide all the information as is the case with your images. No Row Numbers or Column references, no sheet name and often data types and formulas come into play as well.