wildcards in vba
 

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

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

Criteria1:="*" & 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,
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" wrote
in message ...

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

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. :)


--
shellshock
------------------------------------------------------------------------
shellshock's Profile: http://www.excelforum.com/member.php...o&userid=24935
View this thread: http://www.excelforum.com/showthread...hreadid=388836