VBA check if cell contains text string

JungJung

Board Regular
Joined
Jul 13, 2006
Messages
75
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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?
 
Upvote 0
First argument to instr is the start position:

Rich (BB code):
If InStr(1,Worksheets(wks.Name).Range("B2").Value, "Energy",vbTextCompare) = 0 Then    'this is checking that "Energy" doesn't  exist in B2...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top