none
How to do "If cell contains" in VBA

    Question

  • Hi,

      I'm looking for the code to do an if statement when a cell contains "CHECKED OUT"
      I have been using the code below but of course it reads the whole contents of the cell and the cell with say CHECKED OUT by ...

    If ActiveSheet.Range("C6").Value = "CHECKED OUT"

    Thanks,

    Richard
    Monday, September 07, 2009 1:37 PM

Answers

All replies

  • Hi, you need to use the INSTR function e.g.

    If INSTR(1,ActiveSheet.Range("C6").Value , "CHECKED OUT")>0 then
    • Marked as answer by LWCARAB Monday, September 07, 2009 3:19 PM
    Monday, September 07, 2009 1:49 PM
  • ADG already got you fixed but i will post this anyway.  may help in other ways.

    Dim celltxt As String
    celltxt = ActiveSheet.Range("C6").Text
    If InStr(1, celltxt, "CHECKED OUT") Then
    MsgBox ("found it")
    Else
    MsgBox ("no")
    End If
    FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial
    • Marked as answer by LWCARAB Monday, September 07, 2009 3:19 PM
    Monday, September 07, 2009 1:57 PM
  • Thanks, that did just what I wanted!
    Monday, September 07, 2009 2:16 PM
  • I used your code to get the VBA message and it works... too well. Now each time that I try to enter a text in another cell, then the message from the VBA message appears again.

    I tried to implement Boolean but I am too new to VBA. 

    Here is my code (I used Excel 2013 but my colleagues use 2010)

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim celltxt As String
    celltxt = ActiveSheet.Range("E39").Text
    If InStr(1, celltxt, "P670-Staffing") Then

    MsgBox "Add the job title and type of hire in the description cell " & vbNewLine & vbNewLine & "If this is a new position, please obtain HR approval"

    End If

    End Sub

    Thank you for your help

    Tuesday, April 12, 2016 7:31 PM