TRUE or FALSE filldown if contains specific string

[reubanrao93]

Hi guys, I'm trying to find this specific string "ppl" inside column W and fill down column X with TRUE / FALSE text. However it identifies even "apple" or "people" as TRUE as well. How can i make this code to only detect "ppl"?

Thank you in advance


Sub Sample()

Dim LastRow As Long
Dim Ws As Worksheet
Set Ws = ThisWorkbook.Worksheets("Sheet2")
LastRow = Ws.Range("W" & Rows.Count).End(xlUp).Row
If InStr(1, Ws.Range("W1").Text, "ppl", vbBinaryCompare) > 0 Then
Ws.Range("X1:X" & LastRow).Formula = "TRUE"

Else

Ws.Range("X1:X" & LastRow).Formula = "FALSE"
End If
End Sub

 

[maabadi]

Are for you important Arrangement or Don't different "lpp" with "ppl" Or "plp"?

 

[mart37]

Sub Sample()
    Dim Ws As Worksheet
    Dim rngSearch As Range
    Set Ws = ThisWorkbook.Worksheets(Sheet2)
    Set rngSearch = Ws.Range("W1:W" & Ws.Range("W" & Rows.Count).End(xlUp).Row)
    For Each cll In rngSearch
        If InStr(1, cll.Text & ",", "ppl,", vbBinaryCompare) > 0 Then
            Ws.Range("X" & cll.Row).Formula = "TRUE"
        Else
            Ws.Range("X" & cll.Row).Formula = "FAlSE"
        End If
    Next
End Sub

 

[Rick Rothstein]

A couple of questions...

1) Will "ppl" be in the cell all by itself or could there be other text in the cell with it?

2) If there could be other text with it, will the "ppl" always be surrounded by spaces or could there be other characters besides spaces around it (parentheses, dashes, commas, periods, slashes, etc.)?

 

[maabadi]

if Arrangement is important Use this:

Sub FindArrangedLetters()
Dim Lr As Long, N As Long, M As Long
Dim O As Long, Cell As Range
    
 Lr = Cells(Rows.Count, 1).End(xlUp).Row
 
 For Each Cell In Range("A1:A" & Lr)
 M = InStr(1, Cell.Value, "p", 1)
 Debug.Print M
 If M > 0 Then
 N = InStr(M + 1, Cell.Value, "p", 1)
 If N > 0 Then
 Debug.Print N
 O = InStr(1, Cell.Value, "l", 1)
 Debug.Print O
 If O > 0 And O > N And O > M Then
 Cell.Offset(0, 1).Value = True
 GoTo Resum:
 Else
 Cell.Offset(0, 1).Value = False
 GoTo Resum:
 End If
 End If
 End If
 Cell.Offset(0, 1).Value = False
Resum:
 Next Cell
End Sub

 

[reubanrao93]

Sub Sample()
    Dim Ws As Worksheet
    Dim rngSearch As Range
    Set Ws = ThisWorkbook.Worksheets(Sheet2)
    Set rngSearch = Ws.Range("W1:W" & Ws.Range("W" & Rows.Count).End(xlUp).Row)
    For Each cll In rngSearch
        If InStr(1, cll.Text & ",", "ppl,", vbBinaryCompare) > 0 Then
            Ws.Range("X" & cll.Row).Formula = "TRUE"
        Else
            Ws.Range("X" & cll.Row).Formula = "FAlSE"
        End If
    Next
End Sub

Thx man, worked like a charm

 

[Rick Rothstein]

Thx man, worked like a charm

Based on the code mart37 posted, I am guessing (if you had answered my question in Message #4 then I wouldn't have to guess) that you are looking for cells that only contain the three letters "ppl". I would note that, as written, mart37's code will mark as TRUE any cell whose text ends with the letters "ppl". More than likely your text will not have such an ending (although I guess someone could accidentally omit the "e" from something like "I have an appl"... can't tell if that is a possibility with your data or not though). Anyway, if I'm correct about only wanting to mark cells that contain nothing but "ppl" in them, then this non-looping compact macro will also work....

Sub MarkPPLcells()
  With Sheets("Sheet2").Range("W1", Sheets("Sheet2").Cells(Rows.Count, "W").End(xlUp))
    .Offset(, 1) = Evaluate("'" & .Parent.Name & "'!" & .Address & "=""ppl""")
  End With
End Sub