Wildcards in VBA

I'm trying to use a formula that will check the contents of a cell and color the cell's row accordingly. However, the "keywords" that I'm looking for won't be the only thing in the cell - they will most likely be part of a sentence.
With regular formulas, I found that just putting asterisks around the keywork insde quotation marks works fine. In VBA, though, that doesn't seem to work.
Any ideas?

You can use the Like keyword for wildcard comparisons:

If strCellValue Like "*" & strTest & "*" Then...

Be aware that it's case-sensitive, so "A" is not Like "a". If
you need case-insentivity use something like

If LCase(strCellValue) Like "*" & LCase(strTest) & "*" Then...

Andrew


oodam wrote:
> I'm trying to use a formula that will check the contents of a cell and
> color the cell's row accordingly. However, the "keywords" that I'm
> looking for won't be the only thing in the cell - they will most likely
> be part of a sentence.
> With regular formulas, I found that just putting asterisks around the
> keywork insde quotation marks works fine. In VBA, though, that doesn't
> seem to work.
> Any ideas?
>
>
> --
> oodam
> ------------------------------------------------------------------------
> oodam's Profile: http://www.excelforum.com/member.php...o&userid=35513
> View this thread: http://www.excelforum.com/showthread...hreadid=553317

Would the InStr function work for you?

Best regards

John

"oodam" <[email protected]> wrote in message
news:[email protected]...
>
> I'm trying to use a formula that will check the contents of a cell and
> color the cell's row accordingly. However, the "keywords" that I'm
> looking for won't be the only thing in the cell - they will most likely
> be part of a sentence.
> With regular formulas, I found that just putting asterisks around the
> keywork insde quotation marks works fine. In VBA, though, that doesn't
> seem to work.
> Any ideas?
>
>
> --
> oodam
> ------------------------------------------------------------------------
> oodam's Profile:
> http://www.excelforum.com/member.php...o&userid=35513
> View this thread: http://www.excelforum.com/showthread...hreadid=553317
>

Try using Instr:

If InStr(Cells(1, 1), MyStr) > 0 Then MsgBox "Found it"



"oodam" wrote:

>
> I'm trying to use a formula that will check the contents of a cell and
> color the cell's row accordingly. However, the "keywords" that I'm
> looking for won't be the only thing in the cell - they will most likely
> be part of a sentence.
> With regular formulas, I found that just putting asterisks around the
> keywork insde quotation marks works fine. In VBA, though, that doesn't
> seem to work.
> Any ideas?
>
>
> --
> oodam
> ------------------------------------------------------------------------
> oodam's Profile: http://www.excelforum.com/member.php...o&userid=35513
> View this thread: http://www.excelforum.com/showthread...hreadid=553317
>
>

You could try using Instr that will return a value > 0 if the string is
found. e.g.

If Instr(string1, string2) > 0 then
'found
else
'not found
end if

Where string2 is the string to search for in string1. It is case sensitive
as shown
--
Cheers
Nigel



"oodam" <[email protected]> wrote in message
news:[email protected]...
>
> I'm trying to use a formula that will check the contents of a cell and
> color the cell's row accordingly. However, the "keywords" that I'm
> looking for won't be the only thing in the cell - they will most likely
> be part of a sentence.
> With regular formulas, I found that just putting asterisks around the
> keywork insde quotation marks works fine. In VBA, though, that doesn't
> seem to work.
> Any ideas?
>
>
> --
> oodam
> ------------------------------------------------------------------------
> oodam's Profile:
> http://www.excelforum.com/member.php...o&userid=35513
> View this thread: http://www.excelforum.com/showthread...hreadid=553317
>

You probably want to use the InStr function to determine if your substring
exists in the full string. If Instr returns a value then you have found your
substring, otherwise not.

--
HTH...

Jim Thomlinson


"oodam" wrote:

>
> I'm trying to use a formula that will check the contents of a cell and
> color the cell's row accordingly. However, the "keywords" that I'm
> looking for won't be the only thing in the cell - they will most likely
> be part of a sentence.
> With regular formulas, I found that just putting asterisks around the
> keywork insde quotation marks works fine. In VBA, though, that doesn't
> seem to work.
> Any ideas?
>
>
> --
> oodam
> ------------------------------------------------------------------------
> oodam's Profile: http://www.excelforum.com/member.php...o&userid=35513
> View this thread: http://www.excelforum.com/showthread...hreadid=553317
>
>

Thanks everyone for your help. Much to my surprise, it actually seems to be working!

I've created a VBA tool for searching for cells that have certain text appearing somewhere by entering the text into a UserForm textbox and pressing search. It works, but the problem is that I might want every cell where the name Joe Bloggs or J. Bloggs appears, but not Mark Bloggs. Is there a way that I can modify the tool so that the user can enter, for example J* Bloggs in the textbox to get the contents of every cell with Joe Bloggs or J. Bloggs?

Thanks a lot Arlu. Sorry, I was thinking that the rule was to not create a new thread to avoid clutter.