wildcards in vba

[shellshock]

I have an input box where I prompt the user to enter their name.

Then, I use their name as the input for a custom filter that filters out any rows that do not "belong" to them; this way, a massive, intimidating spreadsheet is made to look much simpler.

This is all fine, but there are some rows that "belong" to more than one person. What I want is to use some kind of wildcard function so that the input to the custom filter is actually *username* instead of just username. That way, anything that has their name in it will show up.

Does anyone know how to do this?

This is the VBA I have so far:

Sub Filter_for_Name()

Dim UserName As String
Dim Prompt As String

Prompt = "Enter your last name"
UserName = InputBox(Prompt)

Sheets("Sheet1").Select
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3, Criteria1:=UserName
End Sub

How do I modify this so that UserName is actually *username* instead of just the username?

[shellshock]

This has been solved by concatenating the user input with a pair of asterisks in the following way:

Criteria1:="*" & UserName & "*"

[Henry]

Shellshock,
From VBA Help
~~~~~~~~~~~~~~~~~~~~~~~
Like Operator Example
This example uses the Like operator to compare a string to a pattern.

Dim MyCheck
MyCheck = "aBBBa" Like "a*a" ' Returns True.
MyCheck = "F" Like "[A-Z]" ' Returns True.
MyCheck = "F" Like "[!A-Z]" ' Returns False.
MyCheck = "a2a" Like "a#a" ' Returns True.
MyCheck = "aM5b" Like "a[L-P]#[!c-e]" ' Returns True.
MyCheck = "BAT123khg" Like "B?T*" ' Returns True.
MyCheck = "CAT123khg" Like "B?T*" ' Returns False.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~In your case
MyCheck = UserName Like *username* 'Returns True if username is anywhere
in UserName


Henry

"shellshock" <[email protected]> wrote
in message news:[email protected]...
>
> I have an input box where I prompt the user to enter their name.
>
> Then, I use their name as the input for a custom filter that filters
> out any rows that do not "belong" to them; this way, a massive,
> intimidating spreadsheet is made to look much simpler.
>
> This is all fine, but there are some rows that "belong" to more than
> one person. What I want is to use some kind of wildcard function so
> that the input to the custom filter is actually *username* instead of
> just username. That way, anything that has their name in it will show
> up.
>
> Does anyone know how to do this?
>
> This is the VBA I have so far:
>
> Sub Filter_for_Name()
>
> Dim UserName As String
> Dim Prompt As String
>
> Prompt = "Enter your last name"
> UserName = InputBox(Prompt)
>
> Sheets("Sheet1").Select
> Selection.AutoFilter Field:=1
> Selection.AutoFilter Field:=2
> Selection.AutoFilter Field:=3, Criteria1:=UserName
> End Sub
>
> How do I modify this so that UserName is actually *username* instead of
> just the username?
>
>
> --
> shellshock
> ------------------------------------------------------------------------
> shellshock's Profile:
> http://www.excelforum.com/member.php...o&userid=24935
> View this thread: http://www.excelforum.com/showthread...hreadid=388836
>

[shellshock]

Thanks Henry. I've never used the Like operator before. I'll keep it in mind, although for the application in question it was easiest to modify the custom filter criterion as I showed above.