VBA check if cell contains text string

[JungJung]

Is there a command to check not if a cell exactly matches a text string, but if it contains a text string? There must be!!

If Worksheets(wks.Name).Range("B2").Value *CONTAINS* "Energy" Then...

Thanks.

 

[Andrew Poulsom]

Check out the Instr function in VBA Help.

 

[JungJung]

Thanks Andrew. I swear I searched the forum and VBA help before posting!

So I've changed the code to this:

If InStr(Worksheets(wks.Name).Range("B2").Value, "Energy", 1) = 0 Then...

but it's still not working. Can the InStr function be used like this, or do I need to assign it to a variable and check if the variable is equal to 0?

 

[Richard Schollar]

First argument to instr is the start position:

If InStr(1,Worksheets(wks.Name).Range("B2").Value, "Energy",vbTextCompare) = 0 Then    'this is checking that "Energy" doesn't  exist in B2...

 

[JungJung]

It's supposed to check if "Energy" doesn't exist the way the macro works

I thought the start position argument was optional. Either way, I added it and it's still not working. I'm getting a "subscript out of range" error. As long as I'm using the InStr function correctly I can troubleshoot the rest of the code on my own.

Thanks again.

 

[Richard Schollar]

Hi

What code are you using that doesn't work? Are you getting an error returned (if so what is it)?

 

[JungJung]

I was getting a "subscript out of range" error but I just fixed it! I'm starting to learn some things on my own

Thanks anyways for the help. It's always appreciated.

 

[Drofidnas]

Just found this post and it's helped with what I was wanting to do. If you want to check for "Energy" make the last part <>0.