VBA: A Simple Formula for “If Cell Contains”


You can use the following basic syntax to use a formula for “if cell contains” in VBA:

Sub IfContains()
    Dim i As Integer

    For i = 2 To 8
        If InStr(1, LCase(Range("A" & i)), "turtle") <> 0 Then
        Result = "Contains Turtle"
        Else
        Result = "Does Not Contain Turtle"
        End If
    Range("B" & i) = Result
    Next i
End Sub

This particular example checks if each cell in the range A2:A8 contains “turtle” and then assigns either “Contains Turtle” or “Does Not Contain Turtle” to each corresponding cell in the range B2:B8.

Note: The Instr method checks if one string contains another string and the LCase method converts text to lowercase to perform a case-insensitive search.

The following example shows how to use this syntax in practice.

Example: How to Use “If Cell Contains” in VBA

Suppose we have the following list of cells in Excel that each contain various text:

Suppose we would like to check if each cell in the range A2:A8 contains the text “turtle” and output the results in the corresponding cells in the range B2:B8.

We can create the following macro to do so:

Sub IfContains()
    Dim i As Integer

    For i = 2 To 8
        If InStr(1, LCase(Range("A" & i)), "turtle") <> 0 Then
        Result = "Contains Turtle"
        Else
        Result = "Does Not Contain Turtle"
        End If
    Range("B" & i) = Result
    Next i
End Sub

When we run this macro, we receive the following output:

Column B tells us whether or not the corresponding cells in column A contain “turtle” somewhere in the text.

Note: You can find the complete documentation for the VBA Instr method here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String

2 Replies to “VBA: A Simple Formula for “If Cell Contains””

  1. How could you adjust this so it would read so it would reflect the adjacent cell.

    – If column A equal to “turtle”, then in Column B enter text “turtle”
    — Example: If A1=”turtle”, then enter “turtle” in B1

    – If column A is not equal to “turtle”, then leave Column B text as is/no affect to the the cell because there is other text entered.
    — Example: If A1 is not equal to “turtle”, then ignore

    1. Hi DeAnn…You can achieve this using a simple VBA script that checks the cells in column A and updates the adjacent cells in column B based on the conditions you described. Here’s a step-by-step guide:

      ### Step 1: Open the VBA Editor
      1. Press `Alt + F11` to open the VBA editor.
      2. In the VBA editor, insert a new module:
      – Right-click on any of the existing modules or the `VBAProject` in the left sidebar.
      – Select `Insert` > `Module`.

      ### Step 2: Write the VBA Script
      Copy and paste the following code into the new module:

      “`vba
      Sub UpdateColumnB()
      Dim ws As Worksheet
      Dim lastRow As Long
      Dim i As Long

      ‘ Set the worksheet you want to run the script on
      Set ws = ThisWorkbook.Sheets(“Sheet1”) ‘ Change “Sheet1” to your sheet name

      ‘ Find the last row with data in column A
      lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row

      ‘ Loop through each cell in column A
      For i = 1 To lastRow
      If ws.Cells(i, 1).Value = “turtle” Then
      ws.Cells(i, 2).Value = “turtle”
      End If
      Next i
      End Sub
      “`

      ### Explanation of the Code
      – **Set ws = ThisWorkbook.Sheets(“Sheet1”)**: Set the worksheet where you want to run the script. Replace `”Sheet1″` with the actual name of your worksheet.
      – **lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row**: Find the last row with data in column A.
      – **For i = 1 To lastRow**: Loop through each cell in column A from row 1 to the last row.
      – **If ws.Cells(i, 1).Value = “turtle” Then ws.Cells(i, 2).Value = “turtle”**: If the value in column A equals “turtle”, set the value in the adjacent cell in column B to “turtle”.

      ### Step 3: Run the Script
      1. Close the VBA editor and return to Excel.
      2. Press `Alt + F8` to open the Macro dialog box.
      3. Select `UpdateColumnB` and click `Run`.

      This script will check each cell in column A, and if it contains “turtle”, it will set the corresponding cell in column B to “turtle”. If the cell in column A does not contain “turtle”, the script will leave the cell in column B unchanged.

      ### Additional Note
      If you need the script to update dynamically as you type (i.e., automatically update column B when a change is made in column A), you can use the `Worksheet_Change` event. Here’s how you can do it:

      1. In the VBA editor, double-click the sheet where you want the script to run (e.g., `Sheet1`).
      2. Copy and paste the following code:

      “`vba
      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim ws As Worksheet
      Dim cell As Range

      ‘ Set the worksheet you want to run the script on
      Set ws = ThisWorkbook.Sheets(“Sheet1”) ‘ Change “Sheet1” to your sheet name

      ‘ Check if the change was made in column A
      If Not Intersect(Target, ws.Columns(“A”)) Is Nothing Then
      Application.EnableEvents = False
      For Each cell In Target
      If cell.Value = “turtle” Then
      cell.Offset(0, 1).Value = “turtle”
      End If
      Next cell
      Application.EnableEvents = True
      End If
      End Sub
      “`

      This code will automatically update column B whenever a change is made in column A. Note that it disables events temporarily to avoid triggering the change event recursively.

Leave a Reply

Your email address will not be published. Required fields are marked *