Find part of a word in Advanced Filter Code

I sent this originally to the worksheet funtions newsgroup but then
realised that it is probably more of a programming issue than a funtions
issue. So sorry for the double post...

I'm trying to get an advanced filter to work so that it will bring back
all records
with part of the word rather than just the start of a word. This works
very well when I use AutoFilter with the "Contains" criteria. But when I
do an advanced filter, only records that start with the word get filtered.

I have something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$2" Or _
Target.Address = "$B$2" Then
Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
Unique:=False
End If
End Sub

This is code when you right click the tab. I tried putting "*" in various
places in
this code but can't get it to work. The only way is to put a "*" before
any word I'd
like to search for in the B2 or A2 cell. This works, but I'd like to be
able have it do it automatically so that I just have to type the word I'm
looking for without any "*"'s.

Thanks..

You need to add a criteria range to your advanced filter statement. For example:

Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("E3:E4"), _
Unique:=False

Where the value in E3 is the appropriate column heading, and E4 has a string like *WordYouWant* -
which is the corrollary of autofilter's "Contains"

HTH,
Bernie
MS Excel MVP


"Sierras" <[email protected]> wrote in message
news:[email protected]...
>I sent this originally to the worksheet funtions newsgroup but then realised that it is probably
>more of a programming issue than a funtions issue. So sorry for the double post...
>
> I'm trying to get an advanced filter to work so that it will bring back all records
> with part of the word rather than just the start of a word. This works very well when I use
> AutoFilter with the "Contains" criteria. But when I do an advanced filter, only records that
> start with the word get filtered.
>
> I have something like this:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If Target.Address = "$A$2" Or _
> Target.Address = "$B$2" Then
> Range("Database").AdvancedFilter _
> Action:=xlFilterInPlace, _
> Unique:=False
> End If
> End Sub
>
> This is code when you right click the tab. I tried putting "*" in various places in
> this code but can't get it to work. The only way is to put a "*" before any word I'd
> like to search for in the B2 or A2 cell. This works, but I'd like to be able have it do it
> automatically so that I just have to type the word I'm looking for without any "*"'s.
>
> Thanks..

And if you want to have flexibility in choosing the word, you could start with code like

Range("E4").Value = "*" & InputBox("Word to look for") & "*"

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%[email protected]...
> You need to add a criteria range to your advanced filter statement. For example:
>
> Range("Database").AdvancedFilter _
> Action:=xlFilterInPlace, _
> CriteriaRange:=Range("E3:E4"), _
> Unique:=False
>
> Where the value in E3 is the appropriate column heading, and E4 has a string like *WordYouWant* -
> which is the corrollary of autofilter's "Contains"
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Sierras" <[email protected]> wrote in message
> news:[email protected]...
>>I sent this originally to the worksheet funtions newsgroup but then realised that it is probably
>>more of a programming issue than a funtions issue. So sorry for the double post...
>>
>> I'm trying to get an advanced filter to work so that it will bring back all records
>> with part of the word rather than just the start of a word. This works very well when I use
>> AutoFilter with the "Contains" criteria. But when I do an advanced filter, only records that
>> start with the word get filtered.
>>
>> I have something like this:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Count > 1 Then Exit Sub
>> If Target.Address = "$A$2" Or _
>> Target.Address = "$B$2" Then
>> Range("Database").AdvancedFilter _
>> Action:=xlFilterInPlace, _
>> Unique:=False
>> End If
>> End Sub
>>
>> This is code when you right click the tab. I tried putting "*" in various places in
>> this code but can't get it to work. The only way is to put a "*" before any word I'd
>> like to search for in the B2 or A2 cell. This works, but I'd like to be able have it do it
>> automatically so that I just have to type the word I'm looking for without any "*"'s.
>>
>> Thanks..
>
>

On Fri, 10 Mar 2006 12:44:37 -0500, Bernie Deitrick <deitbe consumer dot
org> wrote:

> Range("E4").Value = "*" & InputBox("Word to look for") & "*"

Seems to work the same with or without the criteria range. Now I have
this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$2" Or _
Target.Address = "$B$2" Then
Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("B1:B2"), _
Unique:=False
End If
End Sub

The line suggested Range("E2").Value = "*" & InputBox("Word to look for")
& "*"
creates a circular that I have to hit Ctrl - Break to get out of. I don't
know where to put this line in order to make it work. In any case, it
would be nicer to be able to just put the word that I'd like to search for
in B2 and have the code put in the *'s before and after without a command
box.

Use this, and type your desired words into either A2 or B2. This assumes that your criteria are
entered into A1:B2 - headers in A1 and B1, and values in A2 and B2.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$2" Or _
Target.Address = "$B$2" Then
Application.EnableEvents = False

Target.Value = "*" & Target.Value & "*"

Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("A1:B2"), _
Unique:=False
Application.EnableEvents = True
End If
End Sub

"Sierras" <[email protected]> wrote in message
news:[email protected]...
> On Fri, 10 Mar 2006 12:44:37 -0500, Bernie Deitrick <deitbe consumer dot org> wrote:
>
>> Range("E4").Value = "*" & InputBox("Word to look for") & "*"
>
> Seems to work the same with or without the criteria range. Now I have this:
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If Target.Address = "$A$2" Or _
> Target.Address = "$B$2" Then
> Range("Database").AdvancedFilter _
> Action:=xlFilterInPlace, _
> CriteriaRange:=Range("B1:B2"), _
> Unique:=False
> End If
> End Sub
>
> The line suggested Range("E2").Value = "*" & InputBox("Word to look for") & "*"
> creates a circular that I have to hit Ctrl - Break to get out of. I don't know where to put this
> line in order to make it work. In any case, it would be nicer to be able to just put the word
> that I'd like to search for in B2 and have the code put in the *'s before and after without a
> command box.

Thank you very much for your time. This is working just as I wanted.

Regards,

Sierras

On Fri, 10 Mar 2006 14:29:50 -0500, Bernie Deitrick <deitbe consumer dot
org> wrote:

> Use this, and type your desired words into either A2 or B2. This
> assumes that your criteria are
> entered into A1:B2 - headers in A1 and B1, and values in A2 and B2.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If Target.Address = "$A$2" Or _
> Target.Address = "$B$2" Then
> Application.EnableEvents = False
>
> Target.Value = "*" & Target.Value & "*"
>
> Range("Database").AdvancedFilter _
> Action:=xlFilterInPlace, _
> CriteriaRange:=Range("A1:B2"), _
> Unique:=False
> Application.EnableEvents = True
> End If
> End Sub
>
> "Sierras" <[email protected]> wrote in message
> news:[email protected]...
>> On Fri, 10 Mar 2006 12:44:37 -0500, Bernie Deitrick <deitbe consumer
>> dot org> wrote:
>>
>>> Range("E4").Value = "*" & InputBox("Word to look for") & "*"
>>
>> Seems to work the same with or without the criteria range. Now I have
>> this:
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Count > 1 Then Exit Sub
>> If Target.Address = "$A$2" Or _
>> Target.Address = "$B$2" Then
>> Range("Database").AdvancedFilter _
>> Action:=xlFilterInPlace, _
>> CriteriaRange:=Range("B1:B2"), _
>> Unique:=False
>> End If
>> End Sub
>>
>> The line suggested Range("E2").Value = "*" & InputBox("Word to look
>> for") & "*"
>> creates a circular that I have to hit Ctrl - Break to get out of. I
>> don't know where to put this
>> line in order to make it work. In any case, it would be nicer to be
>> able to just put the word
>> that I'd like to search for in B2 and have the code put in the *'s
>> before and after without a
>> command box.
>
>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/