If cell contains something then

Question

I am writing a basic macro that would help fill the cell with a value if there is a certain word in the other cell. Here is my code:

Sub SSS()
    For i = 2 To lastrow
        If Cells(i, 4).Text = ("x") Then Cells(i, 2) = "a"
    Next i
End Sub

But cell d2 contains other words as well so if I have "x a" in cell d2 no value is returned in cell b2. How can I fix this?

@AhmedAbdelhameed has simplest best answer - Excel VBA `Like` operator is easiest option to implement. It is basically stripped down regex that is much simpler to use https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/like-operator — dbmitch, May 06 '18 at 18:17
@dbmitch Ahmed's would not only match on exact words but also would match potentially incorrectly on substrings i.e. would match " HelloB rather" in my example. — QHarr, May 06 '18 at 19:31
@vityata: It may be a duplicate of an existing SO question but is it the marked one? The OP hasn't confirmed if a **word** is being sought, as used in description, or if a string match is being sought. — QHarr, May 06 '18 at 19:33
@QHarr - good point - I guess we need to wait for OP to clarify if "contains something" means "anything the same" or "if there is a certain word" — dbmitch, May 06 '18 at 19:47
@dbmitch Yeah...I wasn't sure but at least I have a regex function written now :-) — QHarr, May 06 '18 at 19:48

QHarr · Answer 1 · 2018-05-06T17:21:04.920

You could use regex with word boundary included in the pattern. Assuming it is truly a word that you are looking for.

Code:

Option Explicit
Public Sub SSS()
    Dim lastRow As Long, i As Long
    Const WORD As String = "Hello" '<== Word you are looking for
    lastRow = 6

    With ThisWorkbook.Worksheets("Sheet1")
        For i = 2 To lastRow
            If Found((.Cells(i, 4).Value2), "\b(" + WORD + ")\b", False) Then .Cells(i, 2) = "a"
        Next i
    End With
End Sub
Public Function Found(ByVal t As String, ByVal inputPattern As String, Optional ignoreCaseOption = True) As Boolean
    Dim reg As Object
    Set reg = CreateObject("VBScript.RegExp")
    With reg
        .Global = True
        .MultiLine = True
        .ignoreCase = ignoreCaseOption
        .pattern = inputPattern
        If .test(t) Then Found = True
    End With
End Function

Example data with output:

Regex info:

If cell contains something then

1 Answers1