Results 1 to 7 of 7

VBA check if cell contains text string

This is a discussion on VBA check if cell contains text string within the Excel Questions forums, part of the Question Forums category; Is there a command to check not if a cell exactly matches a text string, but if it contains a ...

  1. #1
    Board Regular
    Join Date
    Jul 2006
    Location
    Albany, NY
    Posts
    70

    Default VBA check if cell contains text string

    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.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,035

    Default Re: VBA check if cell contains text string

    Check out the Instr function in VBA Help.

  3. #3
    Board Regular
    Join Date
    Jul 2006
    Location
    Albany, NY
    Posts
    70

    Default Re: VBA check if cell contains text string

    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?

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,666

    Default Re: VBA check if cell contains text string

    First argument to instr is the start position:

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

    Using xl2013

  5. #5
    Board Regular
    Join Date
    Jul 2006
    Location
    Albany, NY
    Posts
    70

    Default Re: VBA check if cell contains text string

    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.

  6. #6
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,666

    Default Re: VBA check if cell contains text string

    Hi

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

    Using xl2013

  7. #7
    Board Regular
    Join Date
    Jul 2006
    Location
    Albany, NY
    Posts
    70

    Default Re: VBA check if cell contains text string

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com